Wednesday, April 6, 2011

Documenting Your SQL Agent Jobs

XKCD RTFMAs 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):

Documentation Sample in Browser

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)

22 comments:

  1. You're on fire lately Brad, Good job.

    ReplyDelete
  2. Hi Brad !

    Added this script to my library :-)
    Thanks for sharing your work !

    I've added an Index part to it if your interested : http://pastebin.com/Pzefg4jg

    ReplyDelete
  3. Brad, this is amazing. Thanks for sharing. Consider yourself having an open offer for me to buy you a drink sometime.

    ReplyDelete
  4. I don't own an icepick, or I might be at risk.

    Everything about this blog post is fantastic. It's so good, my only regret is that I can't roll in it.

    ReplyDelete
  5. PS: Dear readers:

    If you are like me and want to run this a few times in SSMS, you want to use results to ***GRIDS*** and copy the results from there to save in a .html file.

    If you output to text, you'll have some of the lines truncated, and then you'll stare at your HTML in bewilderment for a few minutes. Like me. :)

    ReplyDelete
  6. @Vincent and @Mike: Thanks so much for the great feedback!

    @Kendra: LOL... Hmmm... now that you mention the icepick, I realize that my blog posts lately have been kinda violent. Last time it was murder, this time stabbings. Sheesh! I'll calm it down, I promise.

    And thanks for mentioning the output to Grid... I'll amend my post to indicate that.

    ReplyDelete
  7. Very nice Brad!! I'm a first time reader. I've been wanting to create a process to do exactly what you've created here. Thanks for sharing you've saved me a ton of time!

    ReplyDelete
  8. Just want to say thank you for this, it's fantastic. Should really save me a lot of time answering questions about what runs when!

    ReplyDelete
  9. Great!!!!! Thanks for this!

    ReplyDelete
  10. Hi Brad,

    Sorry for my ignorance..

    Just want to know what is the meaning of the script below:

    data collector

    step 1

    EXEC [dbo].[sp_syscollector_purge_collection_logs]

    step 2

    dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))"

    ReplyDelete
  11. This looks like a job that was set up automatically by someone setting up a Management Data Warehouse, which collects performance metrics and puts it into a data warehouse for reporting. You can google about it for more information.

    The ESCAPE_DQUOTE with MACH and INST just substitute in your machine name and instance name.

    ReplyDelete
    Replies
    1. Thank you, Brad. Appreciate it.

      Delete
  12. Can you share again your template on another service. Because skydrive's service is down.

    ReplyDelete
    Replies
    1. i found that script on http://themilligans.us/Snippets/--SQL%20STORED%20PROCEDURE%20-%20usp_SQLAgentJobDocumentation.txt

      Delete
  13. Could you please share the code.

    ReplyDelete
  14. Looks like Microsoft changed the links to (what used to be called) SkyDrive. I've updated the link in the post... and here it is right here as well:

    https://onedrive.live.com/?cid=7ba463f684be1dde&id=7BA463F684BE1DDE%21154&authkey=!ADwJCpGdfInIU7U

    This takes you to the public folder of all downloads associated with articles in this blog.

    Let me know if it works for you.

    ReplyDelete
  15. Thank you. You lighted up my way in order to get my jobs documented. Great work!

    ReplyDelete
  16. Hi Brad, I am having trouble exec the script in t-sql...

    Here is what I have

    bcp "exec master.dbo.usp_SQLAgentJobDocumentation"
    queryout "c:\tempd\$(ESCAPE_DQUOTE(MACH))_SQLAgentDocumentation.htm"
    -S$(ESCAPE_SQUOTE(SRVR)) -T -c -w

    ReplyDelete
    Replies
    1. Hi bholmstrom...

      That is an operating system command... it cannot be executed within a query window in SSMS. What you should do is create a SQL Agent job, and the step in the job would execute that command... but instead of defining that step as a Type of "Transact-SQL script (T-SQL)", you would instead indicate that the Type is "Operating System (CmdExec)".

      Delete