By: Eli Leiba | Updated: 2019-07-17 | Comments (1) | 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 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:
And the results are:
- 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.
Last Updated: 2019-07-17
About the author
View all my tips