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.
,
.
Posts
