none
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
    Friday, August 21, 2009 8:45 AM

Answers

All replies

  • Hi,

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

    Regards
    Deepak Goyal
    Friday, August 21, 2009 10:19 AM
  • Full backup does not truncate the log.
    Log backup truncates the log since the last checkpoint for Full or Bulk logged recovery models.
    Friday, August 21, 2009 3:33 PM
  • 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
    Friday, August 21, 2009 7:27 PM
  • 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:

    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.
    Friday, August 21, 2009 8:10 PM
  • Paul,

    Your first two links are not working :(


    Thanks,
    Nimit
    Sunday, August 23, 2009 9:53 PM
  • 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.
    Monday, August 24, 2009 1:38 AM
  • 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
    Monday, August 24, 2009 11:42 AM
  • 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.
    Monday, August 24, 2009 11:49 AM