Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Long Running Jobs

By Leo Peysakhovich,

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.

Total article views: 15420 | Views in the last 30 days: 8
 
Related Articles
FORUM

SQLSERVERAGENT

Agent failes to restart

FORUM

SQL Server Agent Service account locked out

SQL Server Agent Service account locked out

FORUM

How to Share SQL Server Agent Service:

How to Share SQL Server Agent Service:

FORUM

SQLSERVERAGENT service is not running

sqlserveragent service on local computer started and then stopped. some services stop automatically ...

BLOG

Restart the SQL Server Agent Service using PowerShell

Just a quick one today chaps, to show how you can restart the SQL Server Agent Service using PowerSh...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones