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

Question: When was SQL Server last restarted? For the Database Engine, read sqlserver_start_time from sys.dm_os_sys_info.

This is one of my first questions during a Comprehensive Database Performance Health Check. Many useful DMVs lose accumulated history when the engine restarts. A quiet counter just after startup tells a different story from the same counter after a week of work.

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT create_date AS tempdb_create_date
FROM sys.databases
WHERE name = N'tempdb';

EXEC sys.sp_readerrorlog 0, 1, N'SQL Server is starting';

The first query reports the local date and time the current Database Engine started. It does not report Windows boot time or SQL Server Agent’s start time.

Tempdb is recreated during engine startup, so its creation time is another clue. I’ve observed a small difference between that timestamp and the DMV timestamp because they identify different points in startup. I prefer the DMV for the engine’s actual start time.

The third command searches the current SQL Server error log. If the log has cycled since startup, the relevant entry can be in an archived log. Change the log number only after identifying the appropriate archive; an empty search in log 0 doesn’t prove the engine never restarted.

I prefer to investigate an instance that has run long enough to collect representative workload history. Frequent restarts make that harder. Save diagnostic history outside transient DMVs if you need to compare activity across restarts.

Permissions differ by method. The engine DMV requires VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, and VIEW SERVER STATE on earlier versions. Error-log access has its own version-dependent permission requirement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.