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.
This stored procedure depends on the stored procedure: sp_dba_GetSqlJobExecutionStatus.
Here is the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 | CREATE PROCEDURE [dbo].[sp_dba_run_unscheduled_jobs] @job_name_prefix sysname, @max_minutes_runtime int = null, @days_in_job_cycle int = 1, @mail_job_name varchar(256) = '', @mail_profile_name sysname = null, @mail_recipients varchar(max) = null AS BEGIN DECLARE @job_name sysname, @execution_status int, @row_count int, @last_run_date int, @start_time datetime, @stop_time datetime, @min_date datetime, @min_int_date int, @int_date int SELECT @min_date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), @days_in_job_cycle = CASE WHEN @days_in_job_cycle < 1 THEN 1 ELSE @days_in_job_cycle END --Prevent Infinite Loop SELECT @min_int_date = YEAR(@min_date) * 10000 + MONTH(@min_date) * 100 + DAY(@min_date) - @days_in_job_cycle, @int_date = YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()) SELECT @row_count = 1, @job_name = '', @start_time = GETDATE(), @stop_time = DATEADD(mi, @max_minutes_runtime, GETDATE()) WHILE @row_count > 0 AND (@stop_time IS NULL OR @stop_time > GETDATE()) BEGIN SELECT TOP 1 @job_name = sj.name, @last_run_date = sjh.run_date FROM msdb.dbo.sysjobs sj LEFT OUTER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id LEFT OUTER JOIN msdb.dbo.sysschedules ss ON sjs.schedule_id = ss.schedule_id AND ss.[enabled] = 1 LEFT OUTER JOIN ( SELECT job_id, MAX(run_date) AS run_date FROM msdb.dbo.sysjobhistory WHERE step_id = 0 AND run_date > @min_int_date GROUP BY job_id ) sjh ON sj.job_id = sjh.job_id WHERE (sjs.job_id IS NULL OR ss.schedule_id IS NULL) AND sj.name LIKE @job_name_prefix AND (sjh.run_date IS NULL OR sj.name > @job_name OR sjh.run_date < @int_date) AND sj.[enabled] = 1 ORDER BY ISNULL(sjh.run_date, 0), sj.name
SELECT @row_count = @@ROWCOUNT IF @row_count > 0 BEGIN EXEC dbo.sp_dba_GetSqlJobExecutionStatus @job_name = @job_name, @execution_status = @execution_status OUTPUT
IF @execution_status = 4 --Make sure job is not running EXEC msdb.dbo.sp_start_job @job_name = @job_name
WAITFOR DELAY '00:00:02.000' -- Pause here to make sure the job gets started before checking the status EXEC dbo.sp_dba_GetSqlJobExecutionStatus @job_name = @job_name, @execution_status = @execution_status OUTPUT WHILE @execution_status != 4 BEGIN WAITFOR DELAY '00:00:01.000' EXEC dbo.sp_dba_GetSqlJobExecutionStatus @job_name = @job_name, @execution_status = @execution_status OUTPUT END
END
IF @stop_time IS NOT NULL AND @stop_time > GETDATE() BEGIN DECLARE @subject nvarchar(255), @body nvarchar(max) SELECT @subject = @mail_job_name + ' on ' + @@SERVERNAME + ' - Shutting down...Time limit reached.', @body = @mail_job_name + ' on ' + @@SERVERNAME + ' shut down after ' + CAST(ABS(DATEDIFF(mi, GETDATE(), @start_time)) AS nvarchar) + ' minutes.' EXEC msdb.dbo.sp_send_dbmail @profile_name = @mail_profile_name, @recipients = @mail_recipients, @subject = @subject, @body = @body END
END
END GO EXEC sp_MS_marksystemobject 'sp_dba_run_unscheduled_jobs' GO |
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.
