What do Statistics have to do with Scripting Jobs?

We have consistently scripted out our jobs from our Production systems and stored them in source control. We have close to 100 jobs on our main system, which has 4 total copies in an HA environment. We also have support systems that are likewise duplicated with much less jobs. Making quite a few copies of jobs. Lets say 15 systems, with 4 of those systems having 100 jobs. Doing this manually from SSMS will take me some time to accomplish, and only introduces my human element, which much to my chagrin, is not perfect. I cant count the times that I have generated all the jobs, stored them in source control, then as a failsafe simply counted the jobs, and compared against the files I generated only to find i was missing a few. Which ones? And the search is on. Needless to say, this can be a tiresome chore, a time-suck, and an overall frustration in the rear. Why not automate this, you say? Great question. The first few times, we needed these stored in source control in a time sensitive way, and simply did it the awful painful manual way. When time alloted us, we attempted to find some solutions to perform this task in a more easy fashion. We scoured the internet for other solutions and ideas, and settled on a VBscript that uses DMO to produce script files for each job. We plugged this code into a DTS, and away we went, somewhat blindly.

The next time someone on the team needed a job, they could simply refer to source control. There they were all, awaiting some DBA to use them. Said DBA opens the file, and attempts to load the job on a system, only to encounter an error. Odd. Face goes into that half scowl, half frown as they attempt to dig into the job code and understand why it failed. After a few stabs at it, its realized that this code will not work as is. How many other jobs will not work? how long have we been backing up jobs via script and it not working? Well, a smart man would say, since we introduced the automation to save us time. Back to the drawing board to find out what is wrong. In a quick overview, a couple things were wrong with these scripts. There is an sp_Add_Category proc call that was missing a named parameter. @name was not referenced, and the command failed if the category was not present. The servername was included sometimes, and not others. But the worse was that the job steps were not in the proper sequence. The script would attempt to create step 2 before step 1, and, well, SQL Server didnt seem to like that. So, any job that had more than 1 step, was potentially scripted incorrectly, and useless. The other issues were issues, but this step thing was a show stopper. Yeah, back to manual job script generation we go. I can actually script out 100 jobs in about 8 minutes, if i am not interrupted. Click the job, hit F2, copy name to clipboard, hit escape, right click job with mouse, hit S, then C, then F, which will select menu options to Script to file, paste name into File dialog, hit enter, move to next job, repeat. Nice and automated, huh? Not!

A coworker started digging at the VBScript code, as well as other options. After a bit of time, made the discovery that the code was ultimately hitting a table (sysjobs) that had its statistics out of date. Another index was being used, step name, and this was determining the order for the output of the DMO code. After updating statistics on the sysjobs tables, viola! Steps were now in order. Wow. Odd. Wow.

So, we set to talking about this. We had auto statistics configured on this database. However, statistics dont get kicked off until a percentage of the table changes. There are 100 jobs on the system, and they have been there for a long time. We may add 1 or 2, maybe 5 in a year. Not a lot of change. So, I could see how they may have gotten out of whack, and we not even really known, since its such a lowly piece of the system. But with a weird side-effect.

With statistics updated on the systems, and some slight modifications to the VBScript code, we once again have an automated approach to generating Job Scripts for each job, so that they can be saved in Source Control. And this time, we are testing the scripts generated, to ensure that they actually create jobs, and can execute successfully. No more assuming they are good, only to find all the steps out of order.