Get list of failed SQL Server Agent job in SQL Server
When you are working with mission critical database environment, you have to closely keep a watch on every database operation being performed. SQL Server jobs are one of the very important critical things to watch at.
We might have jobs for some:
- heavy aggregation or calculations
- ETL processes
- different kind of alerts and logs
- any much more……..
We may have job running once in a day or many be scheduled to run for few times a day or week. No matter how and when job is running, it is prime duty of DBA to keep watch on each and every job. If any job get failed due to any reason then fix it and run it again.
If job can’t run for some or other reason, it might adversely affect business operation.
Now, question comes, how to keep watch on jobs? You may have many jobs running in SQL Server instance or you may have multiple sql server instance which is executing multiple jobs. I have created on small user defined function and one stored procedure which I execute in SQL Server database and it will give me result of failed job.
If I have recurring job and it failed once but if it runs successfully next time, SP will not give you that job name. Whenever you execute the stored procedure give below, it will check only last occurrence of job, if it is failed, SP will return it to draw your attention towards it.
CREATE FUNCTION [Conv_DateTime](@id int, @it int)
DECLARE @vt char(6), @d char(8), @dt datetime
SELECT @d = convert(varchar, @id)
IF len(@it) = 5
SET @vt = ‘0’ + convert(char,@it)
ELSE if len(@it) = 4
SET @vt = ’00’ + convert(char,@it)
ELSE if len(@it) = 3
SET @vt = ‘000’ + convert(char,@it)
ELSE if len(@it) = 2
SET @vt = ‘0000’ + convert(char,@it)
ELSE if len(@it) = 1
SET @vt = ‘00000’ + convert(char,@it)
ELSE if len(@it) = 6
SET @vt = convert(char,@it)
SELECT @dt = left (@d,4) + ‘-‘ + substring(@d,5,2) + ‘-‘ + right(@d,2) + ‘ ‘ + left(@vt,2) + ‘:’ + substring(@vt, 3,2) + ‘:’ + right(@vt, 2)
Once you are ready with function which will return exact time of when the job failed, we will call that function in following stored procedure.
CREATE PROCEDURE Getfailedjobsdetail
SET NOCOUNT ON
SELECT CAST(Serverproperty(‘servername’) as varchar(100)) AS ServerName,
res.jobname AS JobName,
WHEN 1 THEN RunDuration
WHEN 2 THEN RunDuration
WHEN 3 THEN (cast(Left(right(RunDuration,3),1) as int)*60) + (right(RunDuration,2))
WHEN 4 THEN (cast(Left(right(RunDuration,4),2) AS int)*60) + (right(RunDuration,2))
WHEN 5 THEN (cast(Left(right(RunDuration,5),1) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
WHEN 6 THEN (cast(Left(right(RunDuration,6),2) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
FROM (SELECT Row_number()
partition BY sj.name
ORDER BY run_time DESC) AS rnk,
sj.name AS JobName,
run_duration AS RunDuration,
FROM msdb.dbo.sysjobhistory sjh WITH (nolock)
INNER JOIN msdb.dbo.sysjobs sj WITH (nolock)
ON sjh.job_id = sj.job_id
WHERE sjh.step_id <> 0
AND run_date = CONVERT(VARCHAR(8), Getdate(), 112)) res
WHERE res.run_status = 0
AND res.rnk = 1
After having SP and Function both, you have to execute the stored procedure like this:
If any job is failed and that job is not ran again successfully, above SP will give you list for the same. I used to call above given SP from my monitoring web page, developed in asp.net, I have created. This web page execute SP in all the servers I have maintain and list Job name with Server name if it fails. As soon as support person see anything failed on that web page, they used to come to me to find the issue and fix it.
This is making my life very easy. Istn’t it!!!!!
BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:
- Find SQL Server Agent job ran on specific date with its status (Click Here)
- Search SQL Server Job based on keyword provided (Click Here)
- Script all SQL Server Job automatically by SSIS and SMO (Click Here)
If you like this article, do like “Extreme-Advice” page in Facebook.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.