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

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:

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.