How to determine SQL Server uptime?

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?

 
tempdb
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.

 
sysprocesses
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

 
sys.dm_os_sys_info
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

 
Server startup
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)

 
Dashboard Report
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.

One Response to How to determine SQL Server uptime?

  1. Pingback: Searching through the SQL Server error logs | SQL from the Trenches

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: