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.
-- ============================================================================
-- Stored Procedure to Sequentially Run SQL Agent Jobs
--
-- 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.
--
-- http://adventuresinsql.com/2010/01/stored-procedure-to-sequentially-run-sql-agent-jobs/#comment-116971
-- ============================================================================
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