Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find the Last Windows Server Reboot Time and Last SQL Server Restart


By:   |   Last Updated: 2019-07-17   |   Comments   |   Related Tips: More > Database Administration

Problem

I need to quickly find the last reboot time of the server and the last time the SQL Server service was restarted. In this tip we look at a simple stored procedure you can add to your SQL Server DBA toolbox to do this.

Solution

The solution involves creating a T-SQL stored procedure in the master database, called dbo. usp_FindServerLastRebootDateTime that will get the last time the server was rebooted and the last restart time of the SQL Server service.

In order to get the information for the server reboot, we need to enable xp_cmdshell which will execute the shell command 'wmic os get lastbootuptime'.  The WMIC interface was introduced in Windows 2000 as the command line shell to the WMI subsystem. WMI contains all the information about the local Windows machine. You can get information like Windows version, memory, computer serial number and also the date and time of the last reboot.

The output of the WMIC command is as follows: a 4 digit year, 2 digits for the month, the day, the hour, minutes and seconds. The milliseconds are displayed after the dot sign. The T-SQL procedure extracts the year, month, day, hour, minutes and seconds and outputs it in the output NVARCHAR(20) value. The milliseconds are ignored in the T-SQL output, but can be easily added if required.

The second parameter value is taken from the databases system tables and it equals the creation date and time of the TempDB database that gets recreated each time the SQL Server service is restarted.

SQL Server Stored Procedure to Find Last OS Reboot Date and Last SQL Server Restart Date

Here is the T-SQL code for the stored procedure:

USE master
GO

-- =================================================================================
-- Author:         Eli Leiba
-- Create date:    2019-06
-- Procedure Name: dbo.usp_FindServerLastRebootDateTime
-- Description:    This procedure finds the last OS reboot date and time and  
--                 last restart date and time of the SQL Server service.
-- ==================================================================================
CREATE PROCEDURE dbo.usp_FindServerLastRebootDateTime 
AS
BEGIN
   DECLARE @rebootDT NVARCHAR (20)
   DECLARE @SQLServiceLastRestrartDT NVARCHAR (20)
   DECLARE @dosStmt NVARCHAR (200)
   DECLARE @dosResult TABLE (line NVARCHAR (500))
 
   SET NOCOUNT ON
 
   SET @dosStmt = 'wmic os get lastbootuptime'
 
   INSERT INTO @dosResult EXEC sys.xp_cmdShell @dosStmt
 
   SELECT @rebootDT = CONCAT (
         SUBSTRING (line, 1, 4),'-',
         SUBSTRING (line, 5, 2),'-',
         SUBSTRING (line, 7, 2),' ',
         SUBSTRING (line, 9, 2),':',
         SUBSTRING (line, 11, 2),':',
         SUBSTRING (line, 13, 2)
         )
   FROM @dosResult
   WHERE CHARINDEX ('.', line, 1) > 0
 
   SELECT @SQLServiceLastRestrartDT = CONVERT(NVARCHAR (11), create_date, 23) + ' ' + CONVERT(VARCHAR (8), create_date, 108)
   FROM sys.databases
   WHERE rtrim(ltrim(upper([name]))) = 'TEMPDB'
   
   SELECT @rebootDT as OSServerRebootDateTime, @SQLServiceLastRestrartDT as SQLServiceRestartDateTime
   
   SET NOCOUNT OFF
END
GO

Here is an example using the procedure:

EXEC master.dbo.usp_FindServerLastRebootDateTime 

And the results are:

query output
Next Steps
  • You can create and compile this simple procedure in your master database and use it as a simple tool for information about the server's last reboot and SQL Server service restart dates and times.
  • The procedure was tested on SQL Server 2014 and 2017, but should work with SQL Server 2005 and later.
  • Check that the configuration option for 'xp_cmdshell' equals 1. Run sp_configure and check that config_value = 1 and run_val = 1, like below.
sp_configure values for xp_cmdshell


Last Updated: 2019-07-17


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools