Generating SQL Agent Job Summaries

Posted October 25, 2009

I have a habit of not only categorizing my jobs meticulously, but also prefixing those that are of DBA use/interest only with “DBA: “. It keeps them visually grouped, for both my and others’ benefit. It also lets me run quick little queries like this.

-- summarize dba jobs
select
 [Server] = Convert(varchar, ServerProperty('ServerName')),
 [Category] = c.[name],
 [Job Name] = j.[name],
 [Enabled] = j.[enabled],
 [Step #] = s.[step_id],
 [Step Name] = s.[step_name],
 [Subsystem] = s.[subsystem],
 [Command] = s.[command]
from
 msdb.dbo.sysjobs [j]
 inner join msdb.dbo.syscategories [c] on j.[category_id] = c.[category_id]
 inner join msdb.dbo.sysjobsteps [s] on j.[job_id] = s.[job_id]
where
 j.[name] like 'DBA: %'
order by
 c.[name] asc,
 j.[name] asc,
 s.[step_id] asc ;

Running that gives me a quick overview of what maintenance scripts are setup on a particular server – assuming the prefix is used…