When was SQL Server last restarted?


Jun 08, 2012

Introduction

In this article we will take a look at different ways in which one can easily identify when SQL Server was last restarted.

Different ways to identify when SQL Server was last restarted?

  • Read SQL Server Error Logs using xp_readerrorlog to know SQL Server Restart Time
  • Using Log File Viewer in SQL Server Management Studio to identify SQL Server Restart Time
  • Use sys.dm_os_sys_info SQL Dynamic Management View (DMV) know SQL Server Restart Time
  • Using inbuilt SQL Server Dashboard Reports to get SQL Server Restart Time
  • Read SQL Server Error Logs using sp_readerrorlog to identify SQL Server Restart Time

Let us go through each of the above mentioned options in detail.

Read SQL Server Error Logs using xp_readerrorlog

Database administrator can use xp_readerrorlog Stored Procedure to read SQL Server Error logs to know when SQL Server was last restarted.

USE master
GO
xp_readerrorlog 0, 1, N'SQL Server is Starting at', NULL, NULL, N'asc' 
GO
Last SQL Server Restart by Readibng SQL Server Error Logs using xp_readerrorlog

Parameters supported by XP_READERRRORLOG are:-

1. Error log file number which you would like to read
   a. To read Current SQL Server Error Log specify value as 0
   b. To read Archive #1 SQL Server Error Log specify value as 1
   c. To read Archive #2 SQL Server Error Log specify value as 2
2. Specify whether you want to read SQL Server Error Log or SQL Server Agent Log
   a. To read SQL Server Error Log file specify value as either 1 or NULL
   b. To read SQL Server Agent log specify value as 2
3. Provide Search String 1: Specify Search String Criteria
4. Provide Search string 2: Specify Additional Search String Criteria
5. Provide Start Time for the Search
6. Provide End Time for the Search
7. Specify Sort order for the results
   a. Ascending = N'asc'
   b. Descending = N'desc'


Using Log File Viewer in SQL Server Management Studio

Database administrator can also filter for “SQL Server is Starting at” string in Log View Viewer against SQL Server to know when SQL Server was last restarted as shown in the below snippet.

Identify Last SQL Server Restart Using Log File Viewer in SQL Server Management Studio

Use sys.dm_os_sys_info SQL Dynamic Management View (DMV)

Database administrator can also use sys.dm_os_sys_info Dynamic Management View to know when SQL Server was last restarted. Starting SQL Server 2008 Microsoft added a new column named “SQLSERVER_START_TIME” within sys.dm_os_sys_info DMV which stores the time when SQL Server was last restarted.

Use master
GO

/* DMV sys.dm_os_sys_info added SQLSERVER_START_TIME new column in SQL Server 2008 */

SELECT 
	SQLSERVER_START_TIME AS SQLServerStartTime 
FROM sys.dm_os_sys_info
GO
When SQL Server was Last Restarted sys.dm_os_sys_info

Using inbuilt SQL Server Dashboard Reports

Database Administrator can also use the inbuilt SQL Server Dashboard Report to know when SQL Server was last restarted.

1. In SQL Server Management Studio, right click SQL Server Instance and expand Reports -> expand Standard Reports -> choose Server Dashboard from the drop down list to open SQL Server Dashboard Report.

2. In Server Dashboard report under Configuration Details look for Server Startup Time value as highlighted in the below snippet.

SQL Server Last Restart Time Using Inbuild SQL Server Dashboard Reports

Read SQL Server Error Logs using sp_readerrorlog

Database administrator can also use sp_readerrorlog stored procedure to read SQL Server Error logs to know when SQL Server was last restarted.

USE master
GO
sp_readerrorlog 0, 1, N'SQL Server is Starting at','normal priority'
GO
Read SQL Server Error Logs using sp_readerrorlog to know last SQL Server Restart time

Parameters supported by SP_READERRRORLOG are:-

1. Error log file number which you would like to read
   a. To read Current SQL Server Error Log specify value as 0
   b. To read Archive #1 SQL Server Error Log specify value as 1
   c.To read Archive #2 SQL Server Error Log specify value as 2
2. Specify whether you want to read SQL Server Error Log or SQL Server Agent Log
   a. To read SQL Server Error Log file specify value as either 1 or NULL
   b. To read SQL Server Agent log specify value as 2
3. Provide Search String 1: Specify Search String Criteria
4. Provide Search string 2: Specify Additional Search String Criteria

Conclusion

In this article you have seen how easily a database administrator can quickly identify when SQL Server was last restarted.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."