Stored Procedure to Sequentially Run SQL Agent Jobs

Introduction

Here is another one of my utility scripts. Lately it seems like all I am blogging about is utility scripts, but I have a ton of them and need to get them documented. This stored procedure is used to run a series of SQL Agent jobs sequentially. It is one of my favorite stored procedures because it clears up a lot of headaches for me without having to spend a bunch of money or turn my maintenance schedules over to another team.

The Problem

I was always finding myself rearranging maintenance job schedules to keep them as close as possible to each other without overlapping. Inevitably I would guess wrong and have jobs overlapping in one part of the schedule, hammering a server unnecessarily while having nothing running at other times, wasting my maintenance window. This problem is magnified on multi-instance clusters because jobs can overlap on different instances on the same node, leading to harder to discover performance issues.

I know some people are thinking this sounds like a perfect use for Maintenance Plans, but I have a ton of servers. To have to install and maintain SSIS on each of them would be painful enough, but having to open up every maintenance plan and change it via the GUI on every server for even the smallest change is just too much. I need something I can script and I need to be able to use different parameters on different databases.

The Solution

I wrote a stored procedure, sp_dba_run_unscheduled_jobs, to run all jobs of a given prefix in order, beginning with jobs that have not run most recently and then alphabetically. The main idea of the job is to make maintenance single threaded to lessen the impact of the maintenance on any off-hours user or batch job activity. The stored procedure includes a maximum minutes of run time parameter to stop kicking off new jobs beyond a certain time.

An added benefit to this stored procedured is that the jobs actually get done faster when they are not in contention with each other. When using this stored procedure to run maintenance jobs I use a single job with step 1 running all of the index jobs and step 2 running all of the CheckDB jobs. I have not found a situation where I have had to invoke the time limit.

The stored procedure is written to only run the jobs that do not have a schedule or at least do not have an enabled schedule so I can still add schedules to jobs that I want to run at a certain time. This works out well when I have a reindex and CheckDB for a large database that takes up my entire maintenance window because I can add job scheduled to the long running jobs then just let the maintenance for the other databases run single threaded at the same time.

Recently, I began using this stored procedure to fire off restore jobs. I set up a restore job for each database so I can run the job to restore a single database, including storing permissions, flushing replication etc then putting back everything except replication after the restore. The way the stored procedure works I am able to disable the jobs for the databases I do not want restored then fire off the restore jobs for the remaining jobs sequentially.

The Code

The code for the stored procedure is fairly straightforward. Load a cursor with a list of jobs that begin with the given prefix that are enabled but do not have a schedule or have a disabled schedule. The disabled schedule is important here because it allows the schedule to be retained in case it turns out that job has to run at the scheduled time. Once the cursor is loaded, loop through the list, checking to see if the next job is running. If the job is not running and the current time is not past the end time then start it, pausing before checking if it is running because SQL Agent can take a second to update the status. When the current job stops running grab the next one, check the time and if not past the end time start the job. Repeat until there are no jobs to be run.

Conclusion

This is my new favorite utility stored procedure because I keep finding new uses for it. I hope you find this stored procedure as useful as I do. Please let me know if you run into any issues, have any ideas that would make it better or just want to share how you are using it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.