March 5, 2015 Leave a comment
Determining the SQL Server uptime can be difficult. Because SQL Server is a Windows service that can be stopped and started without restarting the OS, the uptime of your SQL Server can be completely different compared to your server uptime. So how do you determine both uptimes from within SQL Server?
One of the ways to determine the last restart of SQL Server, is by looking at the tempdb. Because the tempdb is recreated on SQL Server startup, you could get an indication of the uptime of your SQL Server, by querying the creation date:
SELECT create_date AS START_TIME_INSTANCE FROM sys.databases WHERE name = 'tempdb'
SQL Server error log
In the SQL Server error log, the startup time is stored on a regular basis, together with a process ID. This information can be retrieved in 2 ways. You can look for either the process id event:
DECLARE @XREL TABLE (LogDate DATETIME, ProcessInfo VARCHAR(100), Text VARCHAR(MAX)) /* Insert current log */ INSERT INTO @XREL EXEC xp_readerrorlog /* Insert previous log */ INSERT INTO @XREL EXEC xp_readerrorlog 1 SELECT TOP 1 * FROM @XREL AS X WHERE X.TEXT LIKE '%Server process ID is%' ORDER BY LogDate DESC
or look for the informational message regarding the process ID:
DECLARE @XREL TABLE (LogDate DATETIME, ProcessInfo VARCHAR(100), Text VARCHAR(MAX)) /* Insert current log */ INSERT INTO @XREL EXEC xp_readerrorlog /* Insert previous log */ INSERT INTO @XREL EXEC xp_readerrorlog 1 SELECT TOP 1 * FROM @XREL AS X WHERE X.Text LIKE '%This instance of SQL Server has been using a process %' ORDER BY LogDate DESC
In the first query, you can look at the LogDate. In the second query, you need to extract the datetime from the Text column.
Another way to find out the startup time, is by looking at the view sys.sysprocesses. This contains information about running processes. And when you look at SPID 1 (system process), you’ll find the startup time of SQL Server:
SELECT login_time AS START_TIME_INSTANCE FROM sys.sysprocesses WHERE spid = 1
The last possibility for SQL Server uptime I want to share is querying the sys.dm_os_sys_info view. Looking at MSDN, this view contains “a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.” Also, the instance startup time:
SELECT sqlserver_start_time AS START_TIME_INSTANCE FROM sys.dm_os_sys_info
Not only SQL Server uptime can be important, but also the server uptime (the hardware on which SQL Server runs). But if you run Windows 8 or Windows Server 2012, this isn’t always accurate. But you can retrieve the accurate with T-SQL:
SELECT DATEADD(MILLISECOND, (sample_ms * -1), GETDATE()) AS BOOT_TIME_MACHINE FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'), 2)
Another way to retrieve the server startup time (without T-SQL, and without using the event viewer in the OS or other tools), is to use the SQL Server Dashboard Report. You can view this report, by right-clicking on the servers name in SQL Server Management Studio (SSMS), and select Reports -> Standard Reports -> Server Dashboard. If you look at the report, in the left table you’ll see the Server Startup time.