Log backup succeeds while Full backup is running but doesn't truncate the log

Question

Log backup succeeds (every 30 minutes) while Full backup is running but doesn't truncate the log. This has been observed, I am wondering why doesn't it put out a warning or something.

Am I missing something here? I would surely appreciate comments from the expert.

---

... wait i'm thinking

Answer 1

Hi,

Can you please tell me SQL server version so I will help you.

Regards
Deepak Goyal

Answer 2

Full backup does not truncate the log.
Log backup truncates the log since the last checkpoint for Full or Bulk logged recovery models.

Answer 3

Thnk about how db backup work....
A checkpoint is issued at beginning. This is then at end used so that all l,og records produced during the bakcup was taken can be included in the db backup. This is needed so the db backup can be restore to a consistent view of the data. I.e., a log backup cannot empty the log (that would cause the db backup logic to fail). It should be able to empty the log for log records that were produced *before* the db backup was initiated, though (methinks).

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 4

V4MC - no, a log backup backs up all log since the last log backup and truncate only the VLFs that can be truncated, regardless of checkpoints. Common misconception.

Tibor - it's actually more than that. All log records required to run recovery are backed up by the full or diff backup - possibly going back way before the full or diff backup started.

Here are some resources to explain all this and help you prove to yourself:

• Blog: More on how much transaction log a full backup includes
• Blog: Misconceptions around the log and log backups: how to convince yourself
• TechNet Magazine: Understanding Logging and Recovery
• TechNet Magazine: Understanding SQL Server Backups

Hope this helps

---

Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.

Answer 5

Paul,

Your first two links are not working :(


Thanks,
Nimit

Answer 6

They are now - for some reason msdn substituted social.msdn.com for www.sqlskills.com

---

Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.

Answer 7

Hi Paul,

Thanks for the links.

Factors That Can Delay Truncation really nailed it.  This is precisely what is happening in our environment.  Third party software took four days to complete a full backup.  We never encountered an error in the hourly transaction log backups because each one is completing successfully.  It turns out unlike 2000, 2005 let the transaction log backup run but doesn't truncate the log if full backup is already running.  In 2000 the behaviour is transaction log backups would be blocked and have to wait for a full backup to complete.

Cheers!

---

... wait i'm thinking

Answer 8

Yup - 2005 is way better. The behavior as far as log truncation is concerned is even better too - in 2000, the log would not truncate until the first (big) log backup after the full completes. In 2005 the log will truncate as soon as the full completes, up to the point backed up by log backups while the full was running.

Thanks

---

Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.