on April 19, 2009
Each time I work with a new system, it can take a while to familiarize myself with what all the SQL Server Agent jobs do. Often there are quite a few jobs, and sometimes they have legacy names that either don’t describe what the job does very well anymore, or is just hard to understand.
Plus, I don’t like opening jobs in the SQL Agent itself very much, since it only opens in an ‘edit’ view. I very much prefer selecting job details out of the tables in msdb, it’s just safer.
Because of this, a while back I wrote a SQL script that takes a lot of descriptive information about a job in MSDB and pivots it out into a table. The table will automatically have as many columns as are required– I have a server where a job has 41 steps, so it’s got 41 columns for step, each in order.
What’s this good for?
This is good to get an overview of jobs on a server– how many steps they have, how complex the steps are, The job description and category, and the basic last run date. I usually paste the results into a spreadsheet, do some minor formatting, and browse through the jobs.
To get a very high level overview, set the @showfullcommands parameter to 0, and it will only show step names, not the commands in the steps.
I also often reference this script when I’m creating other scripts where I want to pivot and dynamically create the number of columns I need.
What is this missing?
This doesn’t describe flow control in jobs. If jobs have lots of complicated “on Fail” and “on Success” actions, you won’t see that in this view. Also, this doesn’t represent start steps or end steps. I worked some of that into initial drafts, but it was just too much information to absorb in this format.
This also is not intended to back up jobs in any way.
The code
-- Display a chart of all jobs with jobsteps
use msdb;
set nocount on;
declare
@query nvarchar(max)
, @selectsql nvarchar(max)
, @pivotsql nvarchar(max)
, @showfullcommands bit
select
@showfullcommands=1 --- change this to 1 to show the full command
--a step is running (not just the step type/db and step name)
, @selectsql=''
, @pivotsql=''
select
@selectsql= @selectsql + '
, Step'+ cast(step_id as nvarchar) + '= coalesce(['+ cast(step_id as nvarchar) + '], '''')'
, @pivotsql=@pivotsql
+ case step_id
when 1
then ''
else ','
end
+ '[' + cast(step_id as nvarchar) + ']'
from (select distinct step_id from msdb.dbo.sysjobsteps) sjs
--print @selectsql
--print @pivotsql
select @query='
select
[Job Name]
, [Enabled]
, [Category Name]
, [Desc]
, [Last Run]
, [Last Outcome]
, Created
, [Last Mod] '
+ @selectsql + '
from (
select
jb.job_id
, [Job Name]=jb.name
, jb.enabled
, [Category Name]=sc.name
, [Desc] = case jb.description
when ''No description available.''
then ''''
else jb.description
end
, created=convert(char(8), jb.date_created, 1)
, [Last Mod]=convert(char(8),jb.date_modified,1)
, [Last Run]= convert (char(8),
(select max(cast(cast(run_date as nvarchar)as datetime))
from msdb.dbo.sysjobhistory jh with (nolock)
where jh.job_id=jb.job_id
and step_id=0)
, 1)
, [Last Outcome]=
(select case run_status
when 0 then ''Failed''
when 1 then ''Success''
when 2 then ''Retry''
when 3 then ''Canceled''
when 4 then ''In progress''
else cast(run_status as nvarchar)
end
from msdb.dbo.sysjobhistory jh with (nolock)
where jh.job_id=jb.job_id
and instance_id =
(select max(instance_id)
from msdb.dbo.sysjobhistory jh2 with (nolock)
where jh2.job_id=jh.job_id
and jh2.step_id=0)
)
, js.step_id
, stepDetail=case js.subsystem
when ''CmdExec'' then ''CmdExec''
when ''SSIS'' then ''SSIS''
when ''TSQL'' then js.database_name
else ''?''
end
+ '': ''
+ js.step_name '
+ case @showfullcommands
when 1
then '+'' = '' + char(10) + js.command '
else ''
end
+ '
from msdb.dbo.sysjobs jb with (nolock)
left join msdb.dbo.syscategories sc with (nolock) on
jb.category_id=sc.category_id
left join msdb.dbo.sysjobsteps js with (nolock) on
jb.job_id=js.job_id
) p
PIVOT (
max(stepDetail)
for step_id in (' + @pivotsql + ')
) as pvt
order by [Job Name]
'
--print @query
exec sp_executesql @query
REGISTER