• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

SQL 2005 Transaction Log Shrink/Truncate/Backup

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
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

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?  
0
jpletcher1
Asked:
jpletcher1
  • 2
  • 2
1 Solution
 
chapmandewCommented:
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:

http://blogs.techrepublic.com.com/datacenter/?p=448
0
 
jpletcher1Author Commented:
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.  
0
 
chapmandewCommented:
Yes, you are correct.  So long as you do them frequently enough (log backups) then you will be set on the log file management.  
0
 
jpletcher1Author Commented:
Thanks, that was a good article to explain how tlogs in SQL work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now