Documenting Your SQL Agent Jobs

As a developer, I hate writing documentation.

And people in general hate reading (or just don’t read) documentation or manuals, as indicated by the XKCD comic.

So why in the world would I go through the crazy exercise of writing a stored procedure to generate documentation for SQL Agent Jobs?

Well, for one thing, I guess I must be a masochist.

Second of all, I enjoy a challenge.

Third, I’m not really writing documentation… I’m just, er, regurgitating it.

And fourth, I have to admit that it does come in very handy when I go in to visit a new client and find out what they’ve got running. ”What? You SHRINK your databases every single night? Do you like poking yourself in the head with an icepick too?”

This documentation I’m spitting out is not just a bunch o’ columns that get plopped into an SSMS grid results window… I went the extra mile and created actual HTML code to generate nice professional content suitable for viewing in your favorite browser (and, when printed, it’s also suitable for wrapping fish, training puppies, and lining birdcages).

Here’s a sample of the output (click on the picture to see a larger version):



If that makes you drool, then keep on reading. If it makes you yawn, then go take a nap.

The name of the stored procedure is called usp_SQLAgentJobDocumentation, and you can download it from my SkyDrive and install it into whatever database you wish.

It just generates a (multi-row) single-column NVARCHAR(MAX) result called HTM. You could just run it in SSMS, but the output is not going to do you much good unless you copy/paste it into a text file. (Important note: Direct the output to GRID, not to TEXT, because a TEXT output window can truncate some of the output. Once it has output to GRID, click in the grid and do a Select All (CTRL+A) and then Copy/Paste).

You can create a (CmdExec) job that uses BCP to output the procedure’s contents to a file, as in the following example:

bcp "exec YOURDATABASEHERE.YOURSCHEMAHERE.usp_SQLAgentJobDocumentation" 
    queryout "X:\SomeFolder\$(ESCAPE_DQUOTE(MACH))_SQLAgentDocumentation.htm" 
    -S$(ESCAPE_SQUOTE(SRVR)) -T -c -w

(If you have a local named instance, you may want to add $(ESCAPE_DQUOTE(INST)) to the filename).

Alternately, you can create an SSIS Package that will execute the stored procedure and output the contents to a Flat File Destination.

Then, you can browse away in the file to your heart’s content.

I made my best attempt to FULLY document the jobs, including multiple schedules, alerts, all possible notifications, etc… The only thing that is left out is anything involving Target Servers, mainly because I couldn’t test it out, but I imagine it’s as easy as JOINing in the sysjobservers and systargetservers tables of the msdb database. If someone would like to test it for me, leave a comment for me or send me an email and I’ll send off a revised copy that will address that.

One extra tidbit is that the documentation shows the average job duration over the last 100 executions, so you can get a good idea of how long the job takes to run. In addition, each individual Job Step shows the duration of its last execution. Both are spelled out in xx Hours xx Mins xx Secs easy-to-read format.

By the way, I did not include any information as to the last date/time executed or next date/time to be executed, because I figured it would be outdated within a half-hour of producing the documentation.

The procedure is just one huge single SELECT statement, with liberal use of CTE’s and CROSS APPLY’s (so it will only run in SQL2005 and beyond). Currently it will generate HTML for ALL jobs, but if you want to revise it to accept some kind of parameters to filter only certain jobs (by Job_ID or Name or whatever), it’s easy enough to do. The procedure’s first CTE is called SelectedJobs and you can make your modifications there:

alter procedure usp_SQLAgentJobDocumentation
  --Optional parameters here to filter jobs you want
as
with SelectedJobs as
(
  --If you want to add parameters to the procedure to filter out
  --certain jobs, you can do it in the WHERE clause here
  select *
  from msdb.dbo.sysjobs j
  --where job_id=@Job_ID
  --where name like '%'+@JobNamePattern+'%'
  --where category_id=@JobCategoryID
)
...

There were a couple of challenges in putting this together. Namely…

One: In my last blog post, I talked about handling the goofy integer representations of dates and times and durations in the sysjobhistory table.

Two: Books Online is a little spotty in giving detail about some of the msdb table columns, so I had to search around the web or use trial-and-error to find out how some of the columns worked. One example is the flags column in the sysjobsteps table, which (I found) is used to represent the various outputs of a Job Step. Books Online simply says “Reserved” for the description of this column, which is no help whatsoever.

Three: I had to unpivot the various data into the various HTML table structures, but that was easily accomplished via a CROSS APPLY, which I’ll talk about a little next week in my T-SQL Tuesday post. And getting the various HTML output to come out in the correct order was something I had to always keep in mind. I also had to worry about various HTML encodings of the ampersand and less-than and greater-than characters.

Four: Finally, translating all the various sysschedules columns into a readable English phrase was fun, coming up with possible phrases like ”Every 20 Minutes From 6:00am to 10:00pm Every 2 Months on the 3rd Wednesday of the Month”. I realize now that a few others have done this already (like SQLFool Michelle Ufford), but I’m glad I attacked it from scratch anyway. The code from that CTE is below if you’re interested.

I’m sure there are 3rd-party products that produce stuff like this, but I don’t care. I did it because I can, and it was fun. Yes, I’m a SQL nerd.

I hope you find this to be useful. I must admit it’s been a great help to me and my clients.

select schedule_id
      ,name
      ,SchedDesc=TimeOfDay+Frequency+EffDtRange
from msdb.dbo.sysschedules 
cross apply 
  --Translate the dates and times into DATETIME values
  --And translate the times into HH:MM:SSam (or HH:MMam) strings
  (select StDate=convert(datetime
                        ,convert(varchar(8),active_start_date))
         ,EnDate=convert(datetime
                        ,convert(varchar(8),active_end_date))
         ,StTime=convert(datetime
                        ,stuff(stuff(right(1000000+active_start_time
                                          ,6)
                                    ,3,0,N':')
                              ,6,0,N':'))
         ,EnTime=convert(datetime
                        ,stuff(stuff(right(1000000+active_end_time
                                          ,6)
                                    ,3,0,N':')
                              ,6,0,N':'))
  ) F_DtTm
cross apply
  --Translate the times into appropriate HH:MM:SSam or HH:MMam char formats
  (select replace(replace(replace(substring(lower(convert(varchar(30),StTime,109))
                                           ,13,14)
                                 ,N':000',N'')
                         ,N':00a',N'a')
                 ,N':00p',N'p')
         ,replace(replace(replace(substring(lower(convert(varchar(30),EnTime,109))
                                           ,13,14)
                                 ,N':000',N'')
                         ,N':00a',N'a')
                 ,N':00p',N'p')
  ) F_Tms(StTimeString,EnTimeString)
cross apply 
  --What Time of Day? Single Time or Range of Times/Intervals
  (select case 
            when freq_subday_type=0
            then N''
            else case 
                   when freq_subday_type=1
                   then N'At '
                   else N'Every '
                       +convert(nvarchar(10),freq_subday_interval)
                       +' '
                       +case freq_subday_type
                          when 2 then N'Second'
                          when 4 then N'Minute'
                          when 8 then N'Hour'
                        end
                       +case 
                          when freq_subday_interval=1 then N'' else N's' end
                       +N' From '
                 end
                +StTimeString
                +case
                   when freq_subday_type=1
                   then N''
                   else N' to '+EnTimeString
                 end
                +N' '
          end
  ) F_Tm(TimeOfDay)
cross apply
  --Translate Frequency  
  (select case freq_type
            when 1
            then N'One Time Only'
            when 4
            then N'Every '
                +case freq_interval 
                   when 1
                   then N'Day'
                   else convert(nvarchar(10),freq_interval)+N' Days'
                 end
            when 8
            then N'Every '
                +case freq_recurrence_factor
                   when 1
                   then N''
                   else convert(nvarchar(10),freq_recurrence_factor)+N' Weeks on '
                 end
                +stuff(case when freq_interval& 1<>0 then N', Sunday' else N'' end
                      +case when freq_interval& 2<>0 then N', Monday' else N'' end
                      +case when freq_interval& 4<>0 then N', Tuesday' else N'' end
                      +case when freq_interval& 8<>0 then N', Wednesday' else N'' end
                      +case when freq_interval&16<>0 then N', Thursday' else N'' end
                      +case when freq_interval&32<>0 then N', Friday' else N'' end
                      +case when freq_interval&64<>0 then N', Saturday' else N'' end
                      ,1,2,N'')
            when 16
            then N'Every '
                +case freq_recurrence_factor 
                   when 1
                   then N'Month '
                   else convert(nvarchar(10),freq_recurrence_factor)+N' Months '
                 end
                +N'on the '
                +convert(nvarchar(10),freq_interval)
                +case 
                   when freq_interval in (1,21,31)
                   then N'st'
                   when freq_interval in (2,22)
                   then N'nd'
                   when freq_interval in (3,23)
                   then N'rd'
                   else N'th'
                 end
                +N' of the Month'
            when 32
            then N'Every '
                +case freq_recurrence_factor 
                   when 1
                   then N'Month '
                   else convert(nvarchar(10),freq_recurrence_factor)+N' Months '
                 end
                +N'on the '
                +case freq_relative_interval 
                   when  1 then N'1st '
                   when  2 then N'2nd '
                   when  4 then N'3rd '
                   when  8 then N'4th '
                   when 16 then N'Last '
                 end
                +case freq_interval 
                   when  1 then N'Sunday'
                   when  2 then N'Monday'
                   when  3 then N'Tuesday'
                   when  4 then N'Wednesday'
                   when  5 then N'Thursday'
                   when  6 then N'Friday'
                   when  7 then N'Saturday'
                   when  8 then N'Day'
                   when  9 then N'Weekday'
                   when 10 then N'Weekend Day'
                 end
                +N' of the Month'
            when 64
            then N'When SQL Server Agent Starts'
            when 128
            then N'Whenever the CPUs become Idle'
            else N'Unknown'
          end
  ) F_Frq(Frequency)
cross apply
  --When is it effective?
  (select N' (Effective '+convert(nvarchar(11),StDate,100)
         +case  
            when EnDate='99991231'
            then N''
            else N' thru '+convert(nvarchar(11),EnDate,100)
          end
         +N')'           
  ) F_Eff(EffDtRange)