Skip log backup if full backup is running

Question

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?

Answer 1

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

Answer 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.