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
j.name 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 j.name = ‘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

Reference

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s