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 ()

*/