3

On some of my SQL2000 servers, I have occasional blocking and weird conflicts between our full backup and log backup jobs. Based on The answers to this SF question, I need to figure out how to prevent my log file backups from running while my full backups are running on the same database. (Sounds like this was changed in SQL2005, so shouldn't be a problem for them.)

Two strategies occur to me:

  1. Before executing the entire log backup job, query the MSDB job history tables to see if the backup step (step 3 of 5) of our nightly full backup job is currently executing. If it is, skip the log backup entirely for all databases.

  2. Inside the log backup job script, before running a log backup on an individual database, query the sysprocess tables (or something) to see if a LIGHTSPEED BACKUP is already occurring for that specific database. If it is, skip the log backup on that one database only.

The first one would be much easier to implement, but would end up skipping lots of databases it probably could back up without any issues (possibly allowing the logs to grow too much). The second one is more appealing, but I'm not sure exactly how to determine whether a backup is in process for a specific database.

Plus, with the second option there is a slight chance that the FULL backup could start to backup a database that the log backup is already working on, unless I add the same kind of "check code" to the full backup script. (Obviously, I'd want the full to have priority over the log backup job)

Anyone have an existing SQL 2000 script that already does this? Which approach makes the most sense to consider? Or are there other alternatives?

Community
  • 1
BradC
  • 2,200
  • 4
  • 25
  • 35

2 Answers2

2

I ran across this problem too in SQL Server 2000.

What i did was the first job step in a full backup was to run sp_update_job on the log backup job with @enable=0 to disable the job.

The last step in the full backup job, successful or not, was to turn the log backup job back on.

On the rare occasion when all the moons lined up, the log backup would be running when the full backup started and the log backup would fail, but at least it would not keep trying.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • Seems like a workable solution. So if the log backup is running when its job gets disabled, it just stops where it is, and reports failure? Wouldn't want a corrupted log backup or anything. – BradC Aug 19 '09 at 17:28
  • it won't stop once started, but it fails & doesn't create a log backup. Never got a corrupt log using this method. If you time it right, you can avoid it all together – Nick Kavadias Aug 19 '09 at 18:14
2

The solution I like to use (since I don't like enabling and disabling jobs as this can leave jobs disabled if there are issues) is to query the sysprocesses table looking for a backup. Look for a query running aginst the db_id = the database you are backing up, and the command of BACKUP DATABASE. Your step would look something like this.

IF EXISTS (SELECT *
           FROM master.dbo.sysprocesses
           WHERE dbid = db_id('YourDatabase')
                 AND cmd LIKE 'BACKUP DATABASE%')
BEGIN
     RAISERROR('The full backup is still running.', 16, 1)
END

For when you need to worry about full backups running while a log backup is running, use something like this.

WHILE EXISTS (SELECT * FROM master.dbo.sysprocesses WHERE dbid = db_id('YourDatabase')
                     AND cmd LIKE 'BACKUP DATABASE%')
BEGIN
     WAITFOR DELAY '00:01:00'
END
mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • What about the (admittedly less likely) case of the full backup trying to start on a database where the log backup is occurring? Is that even a risk worth worrying about? – BradC Aug 20 '09 at 12:16
  • I added code to my answer that you could make step 1 of the full backup job. We don't want that job to bail out if there's a log backup running, simply wait for 1 minute, then try again. The log backup probably doesn't take more than a minute or two to complete. – mrdenny Aug 20 '09 at 23:00
  • Awesome, thanks. FYI, "WHERE db_id =" in each script should be "WHERE DBID =" (no underscore). I'd edit myself, but I don't have enough rep. – BradC Oct 05 '09 at 13:32
  • I've corrected the column name. – mrdenny Oct 05 '09 at 22:36