When Was SQL Server Last Restarted? – Interview Question of the Week #225

Question: When Was SQL Server Last Restarted?

Answer: This is the first question I always ask during my Comprehensive Database Performance Health Check. The system restart date is very important to know but even more so, I always want to know when was SQL Server services were last restarted. When SQL Server services are restarted, many of the Dynamic Management Views (DMV) are automatically emptied out and they lose pretty important history related to SQL Server’s performance.

I always like to work with the system which has been running for a while as it has so much good information hidden in DMVs. However, there have been scenarios when I have to work on the system which is not stable and frequently restarted and those have been always a challenge.

Here is the script which lists when was the last SQL Server services restarted:

SELECT sqlserver_start_time 
FROM sys.dm_os_sys_info

You can also know this information by checking when the TempDB was created. Every time when we restart SQL Server services it automatically recreates the TempDB. You can run the following script to know the time of TempDB creation.

SELECT create_date 
FROM sys.databases 
WHERE name = 'tempdb'

There is one more method also exsists where we read error log and figure out the start time of SQL Server.

EXEC sp_readerrorlog 0,1,'SQL Server is Starting'

However, my personal observation says that there is always some seconds difference between the information which we get from dm_os_sys_info and sys.databases. Though the difference in the time is negligible, I like to depend more on the DMV sys.dm_os_sys_info.

Reference: Pinal Dave (https://blog.sqlauthority.com)