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.

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.

9 thoughts on “Stored Procedure to Sequentially Run SQL Agent Jobs

  1. Hi

    I have a stored procedure which takes in parameters. Is it possible to schedule job such kind of stored proc ?
    Thanks
    Dhruv

  2. Hello

    they ask me to run in series about 10 jobs from sql server agent ( with daily execution time)
    how can i do it using your Stored Procedure to Sequentially Run SQL Agent Jobs
    thanks

    1. That is what it does. You could get the execution time from the history tables or add a couple of lines of code to compute it.

  3. does the 2nd SQL job gets started on actual completion of 1st SQL job ? I understand SQL job is asynchronous, it doesn’t wait for the job to finish

  4. does the 2nd SQL job gets started on actual completion of 1st SQL job ? Because SQL job is asynchronous, it doesn’t wait for the job to finish.

Leave a Reply to DHRUV ARORA Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.