SQL Server sheduled jobs overlapping

Question

SQL Server sheduled jobs overlapping

Aleksandar Andjelkovic

SSC Veteran

Points: 202
More actions
May 28, 2004 at 3:24 am

#107889

I have Full database backup job scheduled at 02:00 every day, Differential backup every 2 hours from 00:00:00 to 23:59:59 and Log backup every 15 min from 00:00:00 to 23:59:59. Eventualy all three jobs will have to start at the same time, is there a way to control execution order, for example: Full backup starts before Differential backup and Differential backup starts before transaction log backup. Does Schedule_id plays any role in processing order? Any advise appreciated.

Aleksandar Andjelkovic

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135380 More actions · Answer 1

have the frist step of your fullbackup doing this :

EXEC MSDB.DBO.sp_update_job @job_name = N'MyserverLog_Incremental', @new_name = N'FullbackupProcedure_Disabled - MyserverLog_Incremental', @enabled = 0

and add a last step doing :

EXEC MSDB.DBO.sp_update_job @job_name = N'FullbackupProcedure_Disabled - MyserverLog_Incremental', @new_name = N'MyserverLog_Incremental', @enabled = 1

This way you can keep control if the job is runnable and you keep track of wich job disabled it.

I'm using this method in this setup :

- fullbackup-job

-- disables "logbackup_job incremental"

-- also makes "last" incremental log-backups

-- alters schedule for "logbackup_job with init" to run after x minutes

- logbackup_job with init (run only once after fullbackup)

-- enables "logbackup_job incremental" (as last step)

- logbackup_job incremental

-- runs every x minutes when enabled

To be sure this works I've scheduled these jobs so they do not tend to start at the same time ! (change the minute part)

Regarding the schedule_id for processing order : I don't know, but I wouldn't rely on it

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Aleksandar Andjelkovic SSC Veteran Points: 202 More actions · Answer 2

Thank you for your quick response.

It is interesting that after running for a week Diff Backup on 2 hrs and Log Backup on 15 min (overlapping every 2 hrs) Differential Backup was always executed first and Log Backup second, no error reported (both Diff and Log backups write to the same backup set file on disk). Retry attempts was set to 0 for both jobs. I guess I have problem only with full backup.

Thank you again.