SQL Server Agent supports features allowing the scheduling

of periodic activities on Microsoft® SQL Server™ 2000, or the notification to

system administrators of problems that have occurred with the server.

SQLServerAgent is a Microsoft®

Windows®  service that executes

jobs, monitors Microsoft SQL Server™, and fires alerts. SQLServerAgent is the

service that allows you to automate some administrative tasks. As such, you must

start the SQLServerAgent service before your local or multiserver administrative

tasks can run automatically.  You

can specify some configuration options for SQL Server Agent during SQL Server

installation. The full set of configuration options is available from within SQL

Server Enterprise Manager only. 

The service name of SQLServerAgent applies only to the

Agent service associated with a default instance. SQL Server Agent services

associated with named instances are named SQLAgent$InstanceName. 

That is the description of the SQLServerAgent. Recently I

came across interesting task. My company has 40-50 active servers. Each server

has multiple maintenance, data transfers, monitoring, and other jobs. There are

some articles how to automate control of jobs in event of Job is failing. One of

the articles is in SQLServerCentral.com Monitoring

Failed Job Steps. But with multiple servers another question is arising as

well – “What if SQLServerAgent is not running?” It happened with our

servers even the SQL Server Agent service is property setup to start

automatically during accidental shutdown or server reboot. I am not trying to

explain why it may happen, but simply would like to show the ideas to resolve a

problem. There is a good article by Gregory Larsen “Detecting the State of a SQL Server Agent job”

(http://www.databasejournal.com/features/mssql/article.php/3491201)

But it does not show the ways for the automation. I can’t

imagine that any DBA would like looping through 50-60 servers and checking if a

Job Engine is running. This article will explain in details how to setup an

automated process to control job service itself. 

There are few ways to verify that SQL Server Agent is

running. One way is to make it with simple query that will check when last time

any job was running. If you know that server has a daily database backup job

then it should be some job(s) running within last 24 hours. If there are no jobs

were running during 24-hour period it means that something is wrong and personal

attention is required. It does not really indicating that SQL Server Agent is

not working (not started). Somebody may disable the jobs or jobs are deleted

(can it happened in the completely secured environment?). In addition, to be

able calculate true 24 hour period we have to add a duration time from the last

running job and add reasonable time just in case the job is running longer than

previously. It may not eliminate the situations when job is running much longer

than usually but in this case it may be necessary to find out why the job is

running much longer. (See the article Controlling

Unusually Long Running Jobs). Let’s see the stored procedure code. 

Create procedure CHECK_SQLServerAgent
   @minutes_back int = 1440, -- 24 hours
   , @recipients varchar(128) 

as 
BEGIN 
Declare @num_of_running_jobs int, @dt datetime, @duration int
        ,  @max_run_dt datetime, @message varchar(255), @subject varchar(50)
set @message = 'Check SQL Server Agent on server ' + @@servername + '. It may not running.' +
' No jobs were running during last ' + Cast(@minutes_back as varchar) + ' minutes.'
Set @dt = getdate() 
Select @max_run_dt = MAX( CAST ( Left(cast(run_date as varchar(8)),4) + '/' + 
substring(cast(run_date as varchar(8)), 5,2) + '/' + 
Right(cast(run_date as varchar(8)), 2) + ' ' + 
cast( ((run_time/10000) %100) as varchar ) + ':' + 
cast( ((run_time/100) %100) as varchar ) + ':' +
cast( (run_time %100) as varchar )   as datetime)  )
 from msdb..sysjobhistory  
 where run_status = 1
select @duration = run_duration 
 from msdb..sysjobhistory  
 where run_status = 1 and CAST  ( Left(cast(run_date as varchar(8)),4) + '/' + 
        substring(cast(run_date as varchar(8)), 5,2) + '/' + 
  Right(cast(run_date as varchar(8)), 2) + ' ' +  
  cast( ((run_time/10000) %100) as varchar ) + ':' + 
  cast( ((run_time/100) %100) as varchar ) + ':' + 
  cast(  (run_time %100) as varchar )  as datetime)  =  @max_run_dt
-- add the time last job usually runs
Set @minutes_back = @minutes_back + 
    Cast( substring(cast(@duration as varchar), 1, 
      Cast( right(cast(@duration as varchar),6) as int) * 60 + 
 Cast ( Substring(right( cast(@duration as varchar), 4) ,  1 ,2) as int)) as int)
 
Select @num_of_running_jobs = count(*)
 from msdb.dbo.sysjobhistory     
 Where DateDiff(mi, CAST ( Left(cast(run_date as varchar(8)),4) + '/' + 
                           substring(cast(run_date as varchar(8)), 5,2) + '/' + 
                           Right(cast(run_date as varchar(8)), 2)  + ' ' + 
cast( ((run_time/10000) %100) as varchar ) + ':' + 
cast( ((run_time/100) %100) as varchar ) + ':' +
cast(  (run_time %100) as varchar )    as datetime),@dt) 
<= @minutes_back
IF (@num_of_running_jobs = 0)
 begin
   EXEC master..xp_sendmail @recipients = @recipients, 
 @subject = 'Check SQL Server Agent',
 @message = @message
 end 
END   -- end procedure

This stored procedure can be modified to check if specific

job was not running within last specified number of minutes/hours. For example

if you have a job that is scheduled based on another job call or server alert. I

have such situation with log shipping database synchronization when

synchronization is the first step for the canned reports. If the job is not

running for 2 hours it is an indication that Crystal Enterprise Server did not

run reports.

Another method to control the SQL Server agent is the

command line statement NET START.

NET START command returns the list of active services started on a server.

Let’s see the output of the command:

These Windows 2000 services are started:  
MSSQLSERVER
Net Logon
Server Agents
SNMP Service
SNMP Trap Service
SQLSERVERAGENT
The command completed successfully. 

If server has a named instance of MS SQL Server then

service name will have a named instance as a part of a name and may look as

followed:

MSSQL$MP04

SQLAgent$MP04

This command can be another solution to the problem.

Command file can be created to run the command and analyze the contest of the

output. If word ‘SQLAgent$’ or ‘SQLSERVERAGENT’ is not found in output it means SQL Agent is not running.

Next stored procedure is giving an ability to check it with NET START command

Create procedure CHECK_SQLServerAgent
     @recipients varchar(128) 
as 
BEGIN 
SET NOCOUNT on 
declare @cnt int, @message varchar(255) 
create table #tmp (startedservices varchar(255) ) 
insert into #tmp(startedservices)   
  exec master..xp_cmdshell 'net start' 
select @cnt = count(*)  
 from #tmp 
 where ltrim(rtrim(startedservices)) = 'SQLSERVERAGENT' 
 or ltrim(rtrim(startedservices)) like 'SQLAgent%'
IF (@cnt = 0)
 Begin
   set @message = 'Check SQL Server Agent on server ' + @@servername + 
'. It is not running.'  
   EXEC master..xp_sendmail @recipients = @recipients,
                         @subject = 'Check SQL Server Agent',
 @message = @message
 end 
END   -- end procedure

Next step is create a command (or VBS) file to run one of the stored procedures

I created command file CheckSQLAgent.cmd and CheckSQLAgent.sql files

--*******************************************
-- CheckSQLAgent.sql file
--*******************************************
exec dbo.CHECK_SQLServerAgent @recipients = 'abc@abc.com'
--*******************************************
  
REM******************************************
REM file CheckSQLAgent.cmd
REM*******************************************
@echo off
cd /d e:\directory\CheckSQLAgent 
REM c:\"Program Files"\"Microsoft SQL Server"\80\Tools\Binn\
isqlw -S servername  -d dbname -E -i CheckSQLAgent.sql -o CheckSQLAgent.log
REM*************************************************************** 

And the last step required the creation of a Windows System

scheduled job to run on a daily bases to verify that SQL Server Agent is

running.

Conclusion

It is possible to use VBS script, use CDO mail instead of MAPI to email to recipient. The task can be modified to check if SQL Server is running by using only Windows NT/XP command line language wrapped into single CMD file. But the method of verification is irrelevant. You should automate the jobs and control running SQL Server Agent.