To find out error message for a SQL server job failure

Sometimes SQL Server job view history times out and we are under time pressure to find out the error details and send to the development team. here is the SQL query:

select as ‘JobName’,
s.step_id as ‘Step’,
s.step_name as ‘StepName’,
h.message as ‘Message’,
msdb.dbo.agent_datetime(run_date, run_time) as ‘RunDateTime’,
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as ‘RunDurationMinutes’
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h
ON s.job_id = h.job_id
AND s.step_id = h.step_id
AND h.step_id <> 0
where j.enabled = 1 –Only Enabled Jobs
and = ‘ABCJOBNAME’ –Uncomment to search for a single job
and msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN ’01/18/2016′ and ’01/19/2016′ –Uncomment for date range queries
order by JobName, RunDateTime desc



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s