Rummage through this blog

Wednesday, 24 February 2010

SQL Server Job Rerun Utility

Ever had a server with numerous scheduled tasks that suffers numerous failures? It happens, one day your SQL Server is working then the next several jobs fail, you diligently rerun them all which takes you hours. Perhaps you even find the same problem again, maybe a drive is on the way out causing an intermittent problem, maybe some evil techie turned off that tape drive for purposes known only to him. Maybe your jobs are running but never finishing so you have to cancel some to free up resources.

You've got a problem. You need to fix it.

But you also need a way to quickly and easily rerun all of your outstanding jobs because that can take hours. Microsoft provide
[msdb].[dbo].[sp_start_job] 'job name'
to help out but that's only good for one job.

I've written the following code to rerun any jobs that have failed or been cancelled which fit the given name using the standard like syntax, waiting for a specified delay between each.

Syntax is
usp_ReRunJobs 'job name', 'delay'
where 'job name' is literally the textual name of the job ie if all of your full backups are called 'Full Backup Of xxxx' then 'Full Backup %', alternatively, just '%' to do everything, whilst 'delay' is in the format 'hh:mm:ss' ie '00:00:30' means a 30 second delay between each job.

You might have even tried the retries feature of scheduled jobs - that works wonders except where a job is genuinely broken and the retry impacts on the next job and so on, so usp_ReRunJobs could be scheduled to run daily after the usual job complete as a backstop.

Here's the code  :

IF OBJECT_ID ( 'usp_ReRunJobs', 'P' ) IS NOT NULL
    DROP PROCEDURE usp_ReRunJobs

create procedure usp_ReRunJobs (
      @Name       varchar(255)      = 'Full Backup%',
      @Delay            varchar(8)        = '00:00:60'

      set nocount on

      IF ISDATE('2000-01-01 ' + @Delay + '.000') = 0
        SELECT 'Invalid time ' + @Delay + ',hh:mm:ss, submitted.';
    END else begin

            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 )
            declare @JName                varchar(255)
            declare cJobs cursor fast_forward for
                  from msdb..sysjobs j
                  join  msdb..sysjobhistory j2 on j.job_id = j2.job_id
                  where like @Name
                  and         j.enabled = 1
                  and         j2.instance_id = (
                                                            SELECT            max(h.instance_id)
                                                            FROM        [msdb].[dbo].[sysjobhistory] h
                                                            where       j2.job_id = h.job_id
                  and         not exists (
                                                      select      x.job_id
                                                      from  #xp_results x
                                                      where x.job_id = j.job_id
                                                      and         x.running = 1
                  and         j2.run_status not in ('1','4')
                  order by

            insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner= ''
            open cJobs
            fetch next from cJobs into @JName
            while @@fetch_status = 0
                  select 'Started '+@JName
                  exec msdb..sp_start_job @Job_Name = @JName
                  waitfor delay @Delay
                  fetch next from cJobs into @JName
            close cJobs
            deallocate cJobs

            drop table #xp_results


I'm not claiming it's the prettiest code or the most efficient but it gets the job done. The only thing I haven't managed to crack is getting the job start message appearing during the execution of the code so if you want to watch your jobs you'll have to resort to activity monitor, I have tried raiserror nowait but that didn't work either but that is a downside I can live with given on the second day of having this code in place it saved me about an hour of rerunning tasks by hand.