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
