Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

How to tell when the SQL Server service was started

Ever wondered if it is possible to programmatically determine when the SQL Server service for an instance was last started? 

It would be nice to have a built-in function that explicitly reports the time of the last service start, but one doesn't exist.

But we can still estimate the time of the service start to within a few seconds, which should be good enough for anything less than the exact calculations required for most strict Service Level Agreement (SLA).

Since we know that tempdb is recreated each time the SQL Server service starts, we can look to the creation date of that system database to provide us the answer.

The following query returns the datetime that the tempdb database was created, allowing us to estimate to within a few seconds the date and time that the service was started.

SELECT         
crdate AS Datetime_Instance_Started
FROM
sysdatabases
WHERE
name = 'tempdb'

If you're running SQL Server 2005, you can also use a Dynamic Management View (DMV) to return the number of milliseconds that have elapsed since the the service was started. The following query illustrates this technique.

USE master
GO

SELECT
TOP 1
sample_ms AS Millisecond_Since_Start
FROM
sys.dm_io_virtual_file_stats(DB_ID(),NULL)

Of course you can convert the milliseconds column to seconds by dividing by 1000, or to minutes by dividing by 60,000.  Or you can get really creative and convert to days, hours, minutes, and milliseconds.  I'll leave that to you or to another post.

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


Mladen
2007-10-03
re: How to tell when the SQL Server service was started
slick!

Davide Mauri
2007-10-09
re: How to tell when the SQL Server service was started
An even easier why is to query the sys.dm_exec_sessions where session_id = 1:

select login_time from sys.dm_exec_sessions where session_id = 1

Vadivel
2009-05-14
re: How to tell when the SQL Server service was started
Querying the sysprocesses is the fool-proof method for finding when SQL Server was last started. Check out http://vadivel.blogspot.com/2009/05/how-to-find-when-sql-server-was-last.html

called reverse lookup
2011-03-09
re: How to tell when the SQL Server service was started
That question always crossed my mind. Excellent tip you gave to combining SQL, my database was perfectly clear and efficient.
I convert my column to days, hours, minutes as you said and worked. Great tips!