Find the last time a SQL Instance was restarted
In our environment, we want to make sure our SQL Servers are restarted and the servers rebooted at least once a quarter. This is primarily to apply OS patches and clear out the memory. The following methods will show you how you can check the last time a SQL Server instance was started. While it doesn't always mean the server was rebooted, as long you're not in the habit of restarting your SQL instance manually, the check may be good enough.
Method 1 - Check the System DMVThis is a simple method that is available in SQL Server 2008 and above.
SELECT [sqlserver_start_time] AS [LastStartupDate] FROM [sys].[dm_os_sys_info]
Method 2 - Check the created date of tempDB.This method relies on the fact that tempdb is re-created every time the SQL instance starts. I like this method because it is clean and easy and (to my knowledge) 100% reliable. Use whatever criteria you like or exclude the crdate check altogether to produce the report you are looking for.
SELECT [crdate] AS [LastStartupDate] FROM [dbo].[sysdatabases] WHERE [name] = 'tempdb' AND [crdate] < GETDATE() - 90 --filter results to instances restarted greater than X days GO
Method 3 - Search the SQL Server error log for the process startup time.This method is more versatile in that you can use it to see many other things in the log than just the startup time. I'll leave it you to come up with some other interesting information you can derive from the log.
What other methods do you have for checking the startup time of a SQL instance or even the server itself? How do you use this information? I'm very interested in hearing your ideas as well.
--search the current sql server error log for the the startup process id DECLARE @LogNum TINYINT = 0 --0=current 1=.1 etc DECLARE @LogType TINYINT = 1 --1=SQL 2=Agent DECLARE @ProcessType VARCHAR(64) = 'Server' DECLARE @TextSearch VARCHAR(20) = 'Server process ID is' DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrLog EXEC sys.xp_readerrorlog @LogNum, @LogType, @ProcessType, @TextSearch --grab the first occurrence and report back the timestamp as the last startup DECLARE @LastRestart DATETIME SELECT @LastRestart = MIN(LogDate) FROM @ErrLog SELECT @LastRestart AS [Last Restart] WHERE @LastRestart < GETDATE() - 90 --filter results to instances restarted greater than X days GO