Get SQL Server and Agent Uptime

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 8 Jun 2020

Description

Returns the SQL Server up time and SQL Agent service running status

Code

USE MASTER

GO


DROP PROCEDURE p_GetServerUptime

GO


CREATE PROCEDURE p_GetServerUptime

AS



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

Purpose: To return the SQL server uptime and

SQL Agent status for the server


For SQL 2000 and SQL2005


Author: Andy Hughes


History: 24 Sep 08 - Intiial Issue

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


SET NOCOUNT ON


DECLARE

@CrDate DATETIME,

@Today DATETIME,

@RunTime DATETIME,

@Years INT,

@Days INT,

@Hours INT,

@Minutes INT,

@Seconds INT,

@AgentRunning INT,

@ServerName SYSNAME


-- get the creation date of tempDB - this is created afresh every time the server is started so serves well here

SELECT @CrDate = CrDate

FROM master..SysDatabases (NOLOCK)

WHERE Name = 'TempDB'


SELECT @Today = GETDATE()


SELECT @RunTime = @Today - @CrDate


SELECT

@Years = DATEPART(yy, @RunTime) - 1900,

@Days = DATEPART(dy, @RunTime),

@Hours = DATEPART(hh, @RunTime),

@Minutes = DATEPART(mi, @RunTime)


SELECT @ServerName = @@SERVERNAME


IF EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')

SELECT @AgentRunning = 1

ELSE

SELECT @AgentRunning = 0



SELECT

@ServerName AS ServerName,

@Years AS Years,

@Days AS Days,

@Hours AS Hours,

@Minutes AS Minutes,

@AgentRunning AS AgentRunning