Determining the last time SQL Server was started

Category : Tips User Rating : 5 Stars      Views : 3.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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

Related Articles

The following articles may also be of interest :

Link back to this article : http://www.sqlmatters.com/Articles/Determining the last time SQL Server was started.aspx

Keywords

TSQL,start,restart,log


Comments
Post by Kevin Kelly on Tue 26 Jan 2016 14:05. Report Inappropriate Post

Thanks for your article. Good tips

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered