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





ċ
Andy Hughes,
Jun 8, 2020, 7:40 AM
Comments