I have a pile of SQL 2005 databases that have been around for a very long time and, for varying reasons, experienced transaction log autogrowth. Regular backups now keep the transaction log usage under control, but the files are still large and fragmented.

I'd like to reclaim some of that space and boost performance by reducing the number of VLFs. However, I'd also prefer not to disrupt our scheduled backups. How does DBCC SHRINKFILE() affect the transaction log backup chain? Or does it? Does this change if I add TRUNCATEONLY to the command?

Note that this is a one-time fix. I'm well aware that regularly shrinking logfiles is a Bad Thing - had these databases been configured with rational initial sizes and autogrowth settings and had backups run perfectly every day of their history, this would not be an issue.

2 Answers 2


Per Microsoft Support http://support.microsoft.com/kb/272318, SQL Server shrinks the log file by removing as many VLFs as it can. It does so while trying to hit a target size without messing up your backup chain.

However, if you do use TRUNCATE_ONLY, the sequence gets messed up, so you'll need to either do that right before your scheduled backup or do full backup right after that.

  • This answers my question exactly. Thanks.
    – sh-beta
    Aug 3, 2011 at 17:12

The recommended approach is to choose a relatively quiet time (i.e. minimal log activity) then do the following:

  1. Backup the log
  2. Shrink the log file with DBCC SHRINKFILE with TRUNCATEONLY to get it as small as possible
  3. Run ALTER DATABASE MODIFY FILE to re-size the log to an appropriate size

Appropriate size for the log is of course dependent on your observations of log usage for each database.

  • This is accurate, but doesn't really answer my question as asked. I specifically want to do this without disrupting my scheduled backups. Taking a backup in a different channel just complicates my life more than I'd like.
    – sh-beta
    Aug 3, 2011 at 17:13
  • Can you clarify how the above would disrupt your scheduled backups? If I was looking to automate this across a server estate, I'd determine the log backup location for each database from system tables and write this additional backup there. Aug 3, 2011 at 17:58
  • My scheduled backups are run by a separate piece of software that keeps its own records and isn't smart enough to pick up one-off backups. Long-term, this should be fixed. Short-term, DBCC SHRINKFILE without TRUNCATEONLY is wiping out a sufficient percentage of these VLFs.
    – sh-beta
    Aug 3, 2011 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.