Performance - Get SQL Server Uptime (and SQLAgent Service Status)

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        18 Sep 2008

Description

This function doesn't use any DMV or calls to OS functions, but obtains the information from within SQL Server.
TempDB is created from scratch on every restart, so the creation date for this database equates almost exactly to the time that the server was last restarted.  It may be a few seconds out, but for all but the most demanding scenarios, this date is acceptable.

The SQL  Agent Service status relies on the fact that the service polls msdb on a regular basis and therefore maintains an open (but usually dormant) connection.  The code checks for the presence of this connection.
You'll note that the check matches for 2 possible application name strings - this is because the name changed between SQL 2005 and 2008.  I have not yet been able to check the SQL 2012 name for the connection

Code

Function:


DROP FUNCTION dbo.udf_GetSQLServerUptime
GO

CREATE FUNCTION dbo.udf_GetSQLServerUptime()
RETURNS @SQLServerUptime TABLE
  
(
      
LastStarted         DATETIME,
      
Days                INT,
      
Hours               INT,
      
Minutes             INT,
      
IsAgentRunning      INT
  
)
AS
/***************************************************************
Purpose:   To return the SQL Uptime
           and whether the SQL Agent is currently running

           The following DMVs are used:
               sys.dm_os_sys_info      - contains OS version information
               sys.dm_os_ring_buffers  - contains a record of all currently active ring bufers and their contents        
          
Author:        ChillyDBA
History:   18 Sep 08 - Initial Issue

****************************************************************/

BEGIN

   DECLARE    
          
@CrDate     DATETIME,
          
@Today      DATETIME,
          
@RunTime    DATETIME,
          
@Days       INT,
          
@Hours      INT,
          
@Minutes    INT,
          
@Seconds    INT,
          
@AgentRunning   INT

  
-- get the creation date of tempDB - this iscreated afresh every time the serfver is started so serves well here
  
SELECT @CrDate = CrDate
  
FROM master..SysDatabases (NOLOCK)
  
WHERE Name = 'TempDB'

  
SELECT @Today = GETDATE()

  
SELECT  @RunTime = @Today - @CrDate

  
-- Get the number of days uptime
  
SELECT @Days = DATEPART(dd, @RunTime)

  
SELECT @Hours = DATEPART(hh, @RunTime)

  
SELECT @Minutes = DATEPART(mi, @RunTime)
  

  
IF EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name IN ('SQLAgent - Generic Refresher', 'SQLAgent - Job invocation engine'))
      
SELECT @AgentRunning = 1
  
ELSE
       SELECT
@AgentRunning = 0


  
INSERT @SQLServerUptime
  
(
      
LastStarted,
      
Days,
      
Hours,
      
Minutes,
      
IsAgentRunning  
  
)
  
SELECT
      
@CrDate         AS LastStarted,
      
@Days           AS Days,
      
@Hours          AS Hours,
      
@Minutes        AS Minutes,
      
@AgentRunning   AS IsAgentRunning
      
  
RETURN

END

/*

SELECT * FROM dbo.udf_GetSQLServerUptime ()

*/










ċ
udf_GetSQLServerUptime.sql
(2k)
Andy Hughes,
Aug 23, 2012, 10:49 AM
Comments