Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
This answers my question exactly. Thanks. –  sh-beta Aug 3 '11 at 17:12
add comment

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.

share|improve this answer
    
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 '11 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. –  Mark Storey-Smith Aug 3 '11 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 '11 at 19:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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