How long has my Sql Server been running

Sometimes you want to know how long you Sql Server has been running. There are a number of ways to find that out.

You can start from Sql Server error log. Go all the way to the beginning of your currect error log, assuming you have not recycled the error log manually, and look at the time stamp there. That is the time when Sql Server was last started. Note that Sql Server start time may not necessarily be consistent with server start time. There is the lag, of course, since Sql Server has to wait until the operating system is up first, assuming it is set as auto start. More importantly, it is possible to restart Sql Server service without rebooting the system.

To get it programmatically, you can run this script. It checks the creation time of your tempdb, since tempdb gets reinitialized every time Sql Server is started.

-- Sql Server 2000 and Sql Server 2005
select crdate from sysdatabases where name = 'tempdb'
-- Sql Server 2005
select create_date from sys.databases where name = 'tempdb'

To make it more intuitive, you can run the script below, which will tell you how many days and hours Sql Server has been running. Minutes and seconds information will be truncated. If you need that, modify the script to get it yourself.

-- Sql Server 2000 and Sql Server 2005
select 'Sql Server Service has been running for about '
+ cast((datediff(hh, crdate, getdate()))/24 as varchar(3)) + ' days and ' 
+ cast((datediff(hh, crdate, getdate())) % 24 as varchar(2)) + ' hours' 
from sysdatabases where name = 'tempdb'

-- Sql Server 2005
select 'Sql Server Service has been running for about ' 
+ cast((datediff(hh, create_date, getdate()))/24 as varchar(3)) + ' days and ' 
+ cast((datediff(hh, create_date, getdate())) % 24 as varchar(2)) + ' hours' 
from sys.databases where name = 'tempdb'

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.