T-SQL – Finding SQL Server Service Restart Date-Time

There are many ways to find out the date & time on which SQL Server service was last restarted.

Method-1: Checking SQL Server Error Log

First time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,‘Copyright (c)’

Method-2: Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM
sys.dm_os_sys_info;

Or you can use Using sys.dm_exec_sessions DMV

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

SELECT login_time FROM
sys.dm_exec_sessions

WHERE session_id = 1;

Method-3: Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces

where is_default = 1

Method-4: Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases
WHERE name = ‘tempdb’