Tracking Uptime

Question: I need to track uptime to manage a service level agreement (SLA). How can I determine how long a particular instance of SQL Server has been running?

Answer: SQL Server doesn't offer a built-in system function that will return this information, but uptime is easy to track if you know a little about SQL Server's internal workings. SQL Server uses several connections to manage its internal processes. The SPID=1 connection to SQL Server is always a system connection established when SQL Server starts up. The master...sysprocesses table contains valuable information about all connections, including a column called login_time. According to SQL Server Books Online (BOL), this column contains the "time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is stored."

Run the following T-SQL query for a reliable indicator of how long SQL Server has been running in minutes:

SELECT datediff(mi, login_time, getdate()) FROM master..sysprocesses WHERE spid = 1

This is another example of why understanding the information in SQL Server system tables is important. Sometimes, querying the system tables is the only way to get the information you need.

You can retrieve this same information in other ways. For example, the first line of the SQL Server error log tells you when SQL Server was started. So you could get the information you seek by running sp_readerrorlog and parsing out the date from the first line of output. Or, you could simply open the error log and view it by using a text viewer, which is an especially good option if you can't run a query to collect the information.