When Was SQL Server Last Restarted? – Interview Question of the Week #225

Question: When Was SQL Server Last Restarted?

When Was SQL Server Last Restarted? - Interview Question of the Week #225 sqlserverrestart

Answer: This is the first question I always ask during my Comprehensive Database Performance Health Check. The system restart date is very important to know but even more so, I always want to know when was SQL Server services were last restarted. When SQL Server services are restarted, many of the Dynamic Management Views (DMV) are automatically emptied out and they lose pretty important history related to SQL Server’s performance.

I always like to work with the system which has been running for a while as it has so much good information hidden in DMVs. However, there have been scenarios when I have to work on the system which is not stable and frequently restarted and those have been always a challenge.

Here is the script which lists when was the last SQL Server services restarted:

SELECT sqlserver_start_time 
FROM sys.dm_os_sys_info

You can also know this information by checking when the TempDB was created. Every time when we restart SQL Server services it automatically recreates the TempDB. You can run the following script to know the time of TempDB creation.

SELECT create_date 
FROM sys.databases 
WHERE name = 'tempdb'

There is one more method also exsists where we read error log and figure out the start time of SQL Server.

EXEC sp_readerrorlog 0,1,'SQL Server is Starting'

However, my personal observation says that there is always some seconds difference between the information which we get from dm_os_sys_info and sys.databases. Though the difference in the time is negligible, I like to depend more on the DMV sys.dm_os_sys_info.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DMV, SQL Scripts, SQL Server, SQL TempDB, Starting SQL
Previous Post
How to Execute Query Without Using F5 or Query Toolbar in SSMS? – Interview Question of the Week #224
Next Post
When was Table Accessed Last By User? – Interview Question of the Week #226

Related Posts

2 Comments. Leave new

  • Jorge Flores
    June 1, 2019 4:37 am

    I use this one too:

    SELECT @@SERVERNAME as Instance, login_time AS ‘Started’,
    DATEDIFF(DAY, login_time, CURRENT_TIMESTAMP)
    AS ‘Uptime in days’
    FROM
    sys.sysprocesses
    WHERE spid = 1;
    Go

    Reply
  • maheswarareddy
    July 6, 2019 5:16 pm

    what is the use index seek in how does the architecture can be defined

    Reply

Leave a Reply