Here are some ways to know a time when SQL server last Restart?
Below is some way that i came across while i was working on this.
- sys.dm_os_sys_info has new column sqlserver_start_time since SQL Server 2008.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
- Here to is another way from creation of Tempdb. We know Tempdb is created again when server restarts.
SELECT crdate AS sqlserver_start_time FROM sysdatabases WHERE name = 'tempdb'
- Another way is to read from xp_readerrorlog(But Beware what if someone cleared ErrorLog by executing “EXEC sp_cycle_errorlog ;”.
DECLARE @logger TABLE (dt datetime, Process varchar(50), txt varchar(MAX)) INSERT @logger exec xp_readerrorlog SELECT TOP 1 sqlserver_start_time FROM @logger
Here are some other way by which we can have near by time of SQl Server Restart.
- First Session entry in sys.dm_exec_sessions.
SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1
- Lazy Writer Login time.
SELECT login_time FROM master..sysprocesses WHERE cmd = 'LAZY WRITER'