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:
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.
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?