SQL 2005 Transaction Log Shrink/Truncate/Backup

Posted on 2010-01-06
Last Modified: 2012-06-22
When using the the Full Recovery Model for databases in SQL 2005, the log files seem to continuously grow, even when both the databases and logs are backed up on a schedule.  I have read several articles on ways to resolve this, such as setting up a maintenance plan with the commands below:

USE DatabaseName
DBCC SHRINKFILE(<TransactionLogName>, 1)
DBCC SHRINKFILE(<TransactionLogName>, 1)

Then I also read that you should never use the TRUNCATE_ONLY command..

This seems like it would be a common issue for anyone using SQL 2005.  Is there something  I'm missing or how do most people solve this issue?  
Question by:jpletcher1
    LVL 60

    Accepted Solution

    you don't have to do this....if you're in full and not doing log backups, then set your recovery mode to simple.  

    otherwise, check this out to get an idea of what is happening:

    Author Comment

    Got it, so as long as we are doing transaction logs throughout the day then the log files should stay relatively smaller in most cases since the backup of them clears space out but doesn't shrink them?  In that case, I'll go through and shrink them all initially now and then make sure they are getting backed up on an 2 hour or so basis.  
    LVL 60

    Expert Comment

    Yes, you are correct.  So long as you do them frequently enough (log backups) then you will be set on the log file management.  

    Author Closing Comment

    Thanks, that was a good article to explain how tlogs in SQL work.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now