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

go

create procedure usp_ReRunJobs (

      @Name       varchar(255)      = 'Full Backup%',

      @Delay            varchar(8)        = '00:00:60'

)

as

begin

      set nocount on

      IF ISDATE('2000-01-01 ' + @Delay + '.000') = 0

    BEGIN

        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

                  select      j.name

                  from msdb..sysjobs j

                  join  msdb..sysjobhistory j2 on j.job_id = j2.job_id

                  where j.name 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 j.name

            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

            begin

                  select 'Started '+@JName

                  exec msdb..sp_start_job @Job_Name = @JName

                  waitfor delay @Delay

                  fetch next from cJobs into @JName

            end

            close cJobs

            deallocate cJobs

            drop table #xp_results

      end

end

go

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.