• subscribe
Search
Browse By: Author | Issue
March 17, 2003 12:00 AM

Tracking Uptime

Brian Moran
SQL Server Pro
InstantDoc ID #38042

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?

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.



ARTICLE TOOLS

Comments
Add A Comment
  • Colamatteo
    2 years ago
    Aug 16, 2010

    Nice post. Here is a script to display server uptime in years, days, hours, minutes and seconds:

    DECLARE @server_start_time DATETIME,
    @seconds_diff INT,
    @years_online INT,
    @days_online INT,
    @hours_online INT,
    @minutes_online INT,
    @seconds_online INT ;

    SELECT @server_start_time = login_time
    FROM master.sys.sysprocesses
    WHERE spid = 1 ;

    SELECT @seconds_diff = DATEDIFF(SECOND, @server_start_time, GETDATE()),
    @years_online = @seconds_diff / 31536000,
    @seconds_diff = @seconds_diff % 31536000,
    @days_online = @seconds_diff / 86400,
    @seconds_diff = @seconds_diff % 86400,
    @hours_online = @seconds_diff / 3600,
    @seconds_diff = @seconds_diff % 3600,
    @minutes_online = @seconds_diff / 60,
    @seconds_online = @seconds_diff % 60 ;

    SELECT @server_start_time AS server_start_time,
    @years_online AS years_online,
    @days_online AS days_online,
    @hours_online AS hours_online,
    @minutes_online AS minutes_online,
    @seconds_online AS seconds_online ;

You must log on before posting a comment.

Are you a new visitor? Register Here

© 2012 Penton Media, Inc.

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.