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 MASTERGODROP PROCEDURE p_GetServerUptimeGOCREATE PROCEDURE p_GetServerUptimeAS/********************************************** 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 ONDECLARE @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 = CrDateFROM master..SysDatabases (NOLOCK)WHERE Name = 'TempDB'SELECT @Today = GETDATE()SELECT @RunTime = @Today - @CrDateSELECT @Years = DATEPART(yy, @RunTime) - 1900, @Days = DATEPART(dy, @RunTime), @Hours = DATEPART(hh, @RunTime), @Minutes = DATEPART(mi, @RunTime)SELECT @ServerName = @@SERVERNAMEIF EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')SELECT @AgentRunning = 1ELSESELECT @AgentRunning = 0SELECT @ServerName AS ServerName, @Years AS Years, @Days AS Days, @Hours AS Hours, @Minutes AS Minutes, @AgentRunning AS AgentRunning