SQL transaction log backups conflicting with full backups?

Posted by BradC on Server Fault See other posts from Server Fault or by BradC
Published on 2009-05-27T16:42:58Z Indexed on 2010/06/17 10:43 UTC
Read the original article Hit count: 439

Filed under:
|
|
|

On our SQL servers (2000, 2005, and 2008), we run full backups once a day in the evening, and transaction log backups every 2 hrs. We haven't really worried about these two processes conflicting, but lately we've run into some of the following issues:

  1. On one server, the trans log backup occasionally blocks the full backup, and must be manually stopped before the full backup can complete

  2. We sometimes end up with a massively-sized trans log backup file (sometimes larger than the full backup!) that seems to occur at the same time the full backup is running.

I found a reference that indicate that these are "not allowed" to run at the same time, whatever that means: SQL 2000 Books Online and SQL 2005 Books Online. I'm not sure whether that means that the server will simply prevent them from running simultaneously, or if we ought to be explicitly stopping the log backups while the full backups are running.

So are there known conflicts/issues between these? Does the answer differ between SQL versions? Should I have the trans log backup job check to see if the full backup is running before it executes? (and how do I do that...?)

© Server Fault or respective owner

Related posts about sql-server

Related posts about backup