• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • 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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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