One notable omission from the server properties available from the SERVERPROPERTY() function is the date and time that SQL Server was last started or restarted. Hopefully this will be resolved in a future version but in the meantime here are a few ways to determine this date. They all work on SQL Server 2005 and 2008.
Using sysprocesses
This is probably the most reliable method, and relies on the fact that SQL Server has a number of internal background tasks that commence when SQL Server starts.
SELECT MIN(login_time) FROM sys.sysprocesses
SELECT MIN(login_time) FROM sys.sysprocesses
Using TempDB Creation Time
I’ve included this here, as I’ve seen others using this method. It relies on the fact that TempDB is recreated every time SQL Server is restarted, which is a clever idea, but I’m not sure that it will always be accurate, for instance if TempDB is recovered. However I expect in the vast majority of cases this will yield the correct result.
SELECT create_date FROM sys.databases
WHERE name='tempdb'
Using the SQL Server Error Log
When SQL Server is started (or restarted) a number of events are stored in the event log. We can search this log to find out when SQL Server started. There is an undocumented, but widely known, stored procedure that allows us to search for any event - sp_readerrorlog.
EXEC sp_readerrorlog 0,1,'SQL Server is Starting'
Again I would exercise caution when using this method – the event log can get very large and it’s possible that the message will change with future versions.
Using a Data Management View (DMV) – SQL Server 2008 only
There is a DMV in SQL Server 2008 that gives us the value directly :
SELECT sqlserver_start_time
FROM
sys.dm_os_sys_info
This DMV is available in SQL Server 2005 but it doesn’t include that column, so use with caution !
I ran all the above queries on my 2008 development box and got the following dates (obviously the times will vary a little due to the different methods of calculation):
2010-01-04 14:47:07.780
2010-01-04 14:45:44.743
2010-01-04 14:45:40.280
2010-01-04 14:44:22.823