Useful T-SQL Queries for MSSQL Administration
Unfortunately we had to lay off the individual responsible for managing our Microsoft SQL Server at work. As a result I have been struggling to manage it the best I can as time permits. I’ve written two queries to help me zero-in on problems quickly.
The first section will output any jobs-steps that have failed in the past 24 hours and the second one will output any jobs running longer than 30 minutes (unusual in our environment). This has been tested using Microsoft SQL Server 2005. Comments welcome.
use msdb
declare @days_back as integer
declare @max_minutes as integer
select @max_minutes = 30
select @days_back = 1
print ‘Job Steps Failing’
select left([name],50) as Job,
step_id as Step,
left(step_name,50) as [Description],
[message] as Error
from msdb..sysjobhistory join msdb..sysjobs on (sysjobs.job_id = sysjobhistory.job_id)
where run_date > convert(varchar(8), getdate()-@days_back,112)
and run_status = 0 — Failed
and step_id <> 0 — Exclude overall job failure
print ‘Job Steps Running Long’
select left([name],50) as Job, step_id as Step,
left(step_name,50) as [Description],
run_duration/60 as Duration
from msdb..sysjobhistory join msdb..sysjobs on (sysjobs.job_id = sysjobhistory.job_id)
where run_date > convert(varchar(8), getdate()-@days_back,112)
and run_duration > @max_minutes*60
and step_id <> 0 — Exclude overall job duration
order by run_duration desc
