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

By:   |   Comments (1)   |   Related: More > Database Administration


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.


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

-- =================================================================================
-- 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 
   DECLARE @rebootDT NVARCHAR (20)
   DECLARE @SQLServiceLastRestrartDT NVARCHAR (20)
   DECLARE @dosStmt NVARCHAR (200)
   DECLARE @dosResult TABLE (line NVARCHAR (500))
   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

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, September 11, 2019 - 7:12:34 AM - Vicky Sukhwani Back To Top (82358)


Can you please provide me same SP for SQL 2008 R2 version as CONCAT function is not working in SQL 2008R2. 

get free sql tips
agree to terms