When was SQL Server restarted?

Over the weekend, I interacted with some of the “wanna be” DBAs. During the interaction I checked with them how they would find when was the SQL Server instance restarted? Everyone had a different approach to find out the information. Here are some of the ways to find out the date & time on which SQL Server service was last restarted.

SQL Server Error Log

By making use of sp_readerrorlog, 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)'

This approach is not useful if the SQL Server Error Log is cycled manually.

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;

But this query does not work in versions which are lower than SQL Server 2008.

Using sys.dm_exec_sessions DMV

The following query will get the Login time of the Session Id 1.

SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;

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

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

This approach is useful except for the instances where the Default Trace is explicitly disabled.

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'

The above query works on SQL Server 2005 and above. For SQL Server 2000 the same query will vary a little bit.

SELECT crdate FROM sysdatabases WHERE name='tempdb'

This is the query which I always use to find out when the SQL Server service was restarted.

7 thoughts on “When was SQL Server restarted?

  1. Pingback: Find the Last Reboot time of the Server | SQL Server DBA Diaries of Pradeep Adiga

  2. James

    Though your info is good, I really don’t appreciate your condescending tone when talking about ‘wannabe’ DBA’s. Everyone has to start from somewhere. You never know what you can learn from them dude. You disappoint me with your ‘I am up there, you are down here’ sort of mentality.

    Reply
    1. PradeepAdiga Post author

      James,
      In this post, with “wannabe” I mean budding, inspiring. I had mentioned about them just as a context, which inspired this post. The intention was not to condenscned. I am no SQL Guru. In fact “I am down here” as a SQL Student and will remain so forever.

      Reply
  3. Luke van der Vaart

    Hi Pradeep

    Excellent article! Just one question. When looking for the login time of the first session, can I achieve the same result with sp_who2? The LastBatch time of SPID 1 seems to indicate when the server was last restarted which is equivalent to the result of your Select statement.

    Thanks

    Luke

    Reply

Leave a Reply