What's the uptime of my MSSQL?

  • One the standard way of measure the reliability of a server is the uptime.
  • Microsoft SQL Server keeps tracks of all the processes, when they start and when they end, in the sys.sysprocesses system view of the master database.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Uptime with MSSQL 2005 and MSSQL 2008

  1. SELECT login_time AS 'Started',
  2.        DATEDIFF(DAY, login_time, CURRENT_TIMESTAMP) AS 'Uptime in days'
  3. FROM sys.sysprocesses
  4. WHERE spid = 1;
  5. go
Started                 Uptime in days
----------------------- -----------
2007-11-21 11:38:03.653 1

(1 row(s) affected)
  • Line 2: login_time is the time the process logged into the server.
  • Line 4: spid = 1 indicates a system processes, and that's the time when the Microsoft SQL Server started.

Uptime with MSSQL 2000

  1. SELECT login_time AS 'Started',
  2.        DATEDIFF(DAY, login_time, CURRENT_TIMESTAMP) AS 'Uptime in days'
  3. FROM sysprocesses
  4. WHERE spid = 1;
  5. go
Started                 Uptime in days
----------------------- -----------
2007-11-21 11:38:03.653 1

(1 row(s) affected)
  • The difference between MSSQL 2000 and MSSQL 2005 & MSSQL 2008 is the name of the sysprocesses table for MSSQL 2000 versus sys.sysprocesses for MSSQL 2005 & MSSQL 2008.