Get SQL Server and Agent Uptime

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