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
WHERE   cmd = 'LAZY WRITER'


About these ads

2 thoughts on “SQL Server-When was Sql server last restart?

  1. Also,Check SSMS>SQl Server Agent>Error Logs> First file date is also Last Sql Server Restart date.First file Name like this:”current: date”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s