How to check whether “SQL Agent” is running or not ?

Scenario:  Recently, I was asked by a friend to find the status of SQL server agent job i.e. either it is in running or stopped state and if it is in a stopped state then email it to concerned DBA team.

Approach: Below is the script that I used to check the status of SQL agent:

/* schedule below script as a T-SQL agent job to run every 2 or 5 min depending on your environment. */

IF EXISTS (  SELECT 1
           FROM MASTER.dbo.sysprocesses
           WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
   SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'kinjal.dba test profile',
    @recipients = 'kinjal.dba@gmail.com',
    @body = 'Please check the status of SQL Agent. It is not running !',
    @query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]',
    @subject = 'SQL Agent is not running',
    @attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment
END

Enhancing the above code to a further step to know the uptime for our SQL server and check whether sql server and sql agent both are running or not.

USE master;

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases 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 '+@hr+' hours & '+@min+' minutes'

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

BEGIN

PRINT 'Attention DBA Team ! SQL Server is running but SQL Server Agent is NOT running !!!!'

END

ELSE BEGIN

PRINT 'OK ! SQL Server and SQL Server Agent both are running'

END

 

HTH,

\\K