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.