Sharpen your SQL Skills

DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them,
but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.
For more great resources, browse the Pineapple Place Store.

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 DMV

This 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.
--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
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.
Topics: 
Media Format: