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
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
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’