July 25, 2003 at 7:30 am
I have a 40GB database that has limited space on the hard drive. There are many transactions happening that in a matter of a day or two the Transaction log grows to about 6 to 7GB. I tried setting up a alert on the transaction log backup that would backup the database when it was a certain percentage full, but this kept executing the job so frequently it started killing performance. I do not want to restrict the size because then the log will constantly fill up. Does anyone have any suggestion on what would be the best approach on how to keep the log from growing so big?
July 25, 2003 at 7:58 am
Check out this older thread. It has many pointers for you:
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
July 25, 2003 at 8:11 am
The older thread was basically everything I have done already. As of right now I run transaction log backups every 4 hours which still is not enough. I also had created a shrink job that runs twice a week. The only problem with this is that there is always connections to the database, which will not allow me to go in and shrink the log file through a scheduled job. Is there any other suggestions?
July 25, 2003 at 8:35 am
Well, we discussed a rather undocumented way to shrink the transaction log in another past thread. You can Detach the database, move the transaction log to a backup location, Reattach the database without the log. It will create a new transaction log. This technique was poo pooed by Microsoft but several others proclaimed to use this same method. I have done this thousands of times and never had a problem. I'll see if I can find the URL to that thread.
All the best,
Dale
Edited by - DALEC on 07/25/2003 08:35:26 AM
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
July 25, 2003 at 8:36 am
After you Reattach and are satisfied that everything appears okay, you can delete the old log.
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
July 25, 2003 at 9:16 am
You know, I could't find that topic in this forum (although I swear we discussed it), but I did find it in another forum I'm in (SQLMAG.COM):
http://www.sqlmag.com/Forums/messageview.cfm?catid=3&threadid=16623
All the best,
Dale
All the best,
Dale
Author: An Introduction to SQL Server 2005 Management Studio
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply