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) RETURNS DATETIME AS BEGIN 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) RETURN (@dt) END GO
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 AS BEGIN SET NOCOUNT ON SELECT CAST(Serverproperty('servername') as varchar(100)) AS ServerName, res.jobname AS JobName, CONVERT(varchar(5), DATEADD(ss,(CASE len(RunDuration) 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) END ), conv_datetime(run_date,run_time)),108)as FailedTime FROM (SELECT Row_number() OVER( partition BY sj.name ORDER BY run_time DESC) AS rnk, sj.name AS JobName, run_date, run_time, run_duration AS RunDuration, run_status 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 END GO
After having SP and Function both, you have to execute the stored procedure like this:
EXEC Getfailedjobsdetail GO
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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.