SQL Agent Reset
Ever had a multitude of SQL Agent jobs with dependencies on one another in order for your nightly batch to succeed? Ever had a situation where you forgot to set the steps correctly within the job resulting in the batch failing or not even running at all?
Me too… here’s what I did in order to protect myself from human error.
I created a new agent job called “SQL Agent Reset”. This jobs sole responsibility is to reset all the other agent jobs to a known configuration. It does this with the help of three meta data tables; 2 stored procedures; and a function.
Run this in order to create the tables. Script to create meta-tables
The idea of this is that after correctly configuring all your jobs in management studio, you run an initial script, which in turn populates the three meta tables with all the configuration data, that is required to set the job, the steps, and the schedules. Once you have this data, it is read each time the reset job runs and the steps and schedules are recreated.
The advantages to using this method, are the manageability it allows through use of tables in one database. (i.e. keeping all job data in one place as opposed to cumbersome scripts). Also acts as a useful failsafe. If a job is accidently deleted, then provided the meta data is stored, you can recreate the job.
This is the initial populate script to gather the agent data from msdb and populate your tables.
This is only done provided that the job hasn’t failed previously or isn’t currently running. We wouldn’t want to start reseting jobs when they are trying to tell us something. In order to test this I have created a function.
The next script creates the stored procedure that will take the meta data and use it to reset the agent job. Create this procedure in your control database.
After initial population of the table, there will be new jobs added to the agent that you may wish to reset in the same way. You can populate the meta tables for a new job using this sproc.
Final task to set this up is to create a SQL Agent Job. This is what is going to run before the batch starts. It will contain a step for each job that needs to be reset.
Please note that this was originally written for SQL 2000 and adapted for 2005 in 80 percent compatibilty mode. It has been tested for this, however, may require slight update to script for 90 percent compatability mode.
Happy days.