SQL Server-When was Sql server last restart?

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