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.

Stored Procedure to Get SQL Job Execution Status

Introduction

Ever need a programmatic way to figure out if a SQL Agent job is running? I did. After spending a while searching on Google I did not turn up anything I liked so I turned to Profiler and started looking at what calls SQL Management Studio uses. After a bit of poking around I was able to come up with a stored procedure that I could call.

The Meat

The stored procedure is pretty straightforward. Get the job and owner from the system tables, declare a temporary table, call xp_sqlagent_enum_jobs with the results going into the temporary table, finally placing a return value in an output variable and optionally producing a recordset.
Comments at the top of the stored procedure outline the various statuses as of the time of writing. My general rule is everything except a status of 4 means running.

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
USE [master]
GO

CREATE PROCEDURE [dbo].[sp_dba_GetSqlJobExecutionStatus](@job_name sysname, @select_data int =0, @execution_status int = NULL OUTPUT)

AS

SET NOCOUNT ON

/*
Is the execution status for the jobs.
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions

*/


DECLARE @job_id uniqueidentifier,
@is_sysadmin int,
@job_owner sysname

SELECT @job_id = jv.job_id,
@job_owner = sp.name
FROM msdb.dbo.sysjobs_view jv
INNER JOIN sys.server_principals sp
ON jv.owner_sid = sp.sid
WHERE jv.name = @job_name

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

CREATE TABLE #xp_results
(
job_id uniqueidentifier NOT NULL,
last_run_date int NOT NULL,
last_run_time int NOT NULL,
next_run_date int NOT NULL,
next_run_time int NOT NULL,
next_run_schedule_id int NOT NULL,
requested_to_run int NOT NULL, -- BOOL
request_source int NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running int NOT NULL, -- BOOL
current_step int NOT NULL,
current_retry_attempt int NOT NULL,
job_state int NOT NULL
)

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id

SELECT @execution_status = job_state
FROM #xp_results

DROP TABLE #xp_results

IF @select_data =1
SELECT @job_name AS job_name,
@execution_status AS execution_status

SET NOCOUNT OFF
GO

EXEC sp_MS_marksystemobject 'sp_dba_GetSqlJobExecutionStatus'

Conclusion

I hope you find this stored procedure as useful as I do. Please let me know if you run into any issues or have any ideas that would make it better. 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.

Using sp_ExecuteSql to Run Dynamic SQL


Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Notice: Undefined index: HARDESCAPE in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Warning: Invalid argument supplied for foreach() in C:\home\site\wwwroot\wp-content\plugins\codecolorer\lib\geshi.php on line 2785

Introduction

Lately it seems like I am being bombarded by Dynamic SQL and people who insist it is the only way to accomplish something. Dynamic SQL is almost never the best way to accomplish a given task, but sometimes (and I do mean just sometimes) it is. In the post I will show why to use sp_ExecuteSql and how to convert an existing procedure to use it properly.

The Example

Below is an example stored procedure that I came up with that takes multiple parameters and based on those parameters formulates and executes a query. It is intentionally easy and could well be written otherwise but it works well for this purpose.

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
CREATE PROCEDURE dbo.GetTableInfo @object_id int = null, @name sysname = null
AS
DECLARE @sql_command nvarchar(max)

SELECT @sql_command = N'
SELECT [name],
[object_id]
FROM sys.tables
'


IF @object_id IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [object_id] = ' + CAST(@object_id AS nvarchar)
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'
AND [name] = '
'' + @name + ''''
END
END
ELSE
BEGIN
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [name] = ''' + @name + ''''
END
ELSE
BEGIN
SELECT @sql_command = N''
END
END

PRINT @sql_command
EXEC sp_executesql @sql_command

The stored procedure takes 2 parameters, @object_id and/or @name. Based on the inputs a WHERE clause is created and appended to the rest of the SQL statement. In case anyone tries to pull a fast one to see a full table list by submitting null for both arguments a check has been added to clear the string, causing nothing to be returned. At this point, the example looks fairly robust and somewhat bulletproof.

The Test(s)

Testing the stored procedure should be fairly simple. To keep things easy I will test in msdb by searching for the table sysjobs by name, object_id and then both. I will then test for the possibility of SQL Injection.

Test #1

EXEC dbo.GetTableInfo @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = ‘sysjobs’

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #2

EXEC dbo.GetTableInfo @object_id = 277576027

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = 277576027

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #3

EXEC dbo.GetTableInfo @object_id = 277576027, @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = 277576027
AND [name] = ‘sysjobs’

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #4 – SQL Injection

EXEC dbo.GetTableInfo @name = ‘sysjobs” OR ”1”=”1”; SELECT name FROM sys.server_principals WHERE name != ”’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = ‘sysjobs’ OR ‘1’=’1′; SELECT name FROM sys.server_principals WHERE name != ”

name object_id
—————– —————
syssubsystems 5575058
sysproxysubsystem 21575115
restorefilegroup 30623152
.
.
.

(97 row(s) affected)

name
——————————-
sa
public
sysadmin
.
.
.

(33 row(s) affected)

The Problem

Testing looked great until the SQL Injection test. By mangling the parameters passed into the stored procedure I was able to get it to dump a list of all logins on the server. There is no reason that a call to a stored procedure could not be added in there instead followed by “–” to comment out the rest of the string. If xp_cmdshell were unsecured on this server the box would now belong to anyone that could exploit this vulnerability. This is about as bad as it gets short of leaving the sa password blank.

The Fix

In the example above I made a key error to prove a point. Even though I used sp_ExecuteSql, I used it in a way that was no better than using the EXEC statement. The proper way to use it is to build a parameterized SQL statement and pass the parameters into sp_ExecuteSql seperately. I know this sounds like a lot of extra work but an important thing to note is that all of the parameters can be passed for every call and will only used if they are in the SQL string that is passed in.

The added benefit of parameterized SQL is the elimination of procedure cache bloat. The example above creates a unique query string that will cause a plan to be added to the procedure cache every time the procedure is called unless the same exact parameters are passed. The fixed or improved version will add 3 plans to the procedure cache, slightly more if there are wide variations in the data but far less than the 1 for every call the example will add.

Here is the fixed version:

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
CREATE PROCEDURE dbo.GetTableInfo @object_id int = null, @name sysname = null
AS
DECLARE @sql_command nvarchar(max),
@sql_parameters nvarchar(max)

SELECT @sql_command = N'
SELECT [name],
[object_id]
FROM sys.tables
'
,
@sql_parameters = N'@object_id int, @name sysname'

IF @object_id IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [object_id] = @object_id'
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'
AND [name] = @name'

END
END
ELSE
BEGIN
IF @name IS NOT NULL
BEGIN
SELECT @sql_command = @sql_command + N'WHERE [name] = @name'
END
ELSE
BEGIN
SELECT @sql_command = N''
END
END

PRINT @sql_command
EXEC sp_executesql @sql_command, @sql_parameters, @object_id = @object_id, @name = @name

GO

The Re-Test(s)

Testing the stored procedure should be fairly simple. To keep things easy I will test in msdb by searching for the table sysjobs by name, object_id and then both. I will then test for the possibility of SQL Injection.

Test #1

EXEC dbo.GetTableInfo @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = @name

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #2

EXEC dbo.GetTableInfo @object_id = 277576027

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = @object_id

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #3

EXEC dbo.GetTableInfo @object_id = 277576027, @name = ‘sysjobs’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [object_id] = @object_id
AND [name] = @name

name object_id
—————– —————
sysjobs 277576027

(1 row(s) affected)

Test #4 – SQL Injection

EXEC dbo.GetTableInfo @name = ‘sysjobs” OR ”1”=”1”; SELECT name FROM sys.server_principals WHERE name != ”’

Returns:

SELECT [name],
[object_id]
FROM sys.tables
WHERE [name] = @name

name object_id
—————– —————

(0 row(s) affected)

Conclusion

Dynamic SQL should be considered a highly specialized tool of last resort and used in the way I have described. As I have shown, misuse can lead to your server and the data stored on it no longer belonging to you.