T-SQL: show uptime of SQL server & and status of agent
Interested to see how long your SQL server agent is up and running, or you want to see if that agent is operational? You can use the t-sql code below to find out:
SET NOCOUNT ON DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5) SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb' SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60 IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0 SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE())) ELSE SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60 PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '[email protected]+' hours & '[email protected]+' minutes' IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher') BEGIN PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running' END ELSE BEGIN PRINT 'SQL Server and SQL Server Agent both are running' END