October 29, 2009 at 10:41 am
Our transaction log file has gorwn tremendously. For a 45GB database the transaction log file is 98GB. I have taken a full transaction log backup and then tried to shrink the file but did not help.
Then I read in some other posts where the recovery model was change to SIMPLE from FULL and then transaction log file was shrinked to 0. I tested this on a development server and it did work.
I am not sure what are the pitfalls of above procedure. Is this recommended on production server?
I will definately have to do something otherwise transaction log file will outgrow the disk space.
Please help.
October 29, 2009 at 11:00 am
If you switch to simple recovery, when you switch back to FULL recovery, you must take a new full backup to restablish your log chain to allow point-in-time recovery going forward.
How much free space is in the t-log ?
Here's Gail's article on Managing Transaction Logs: http://www.sqlservercentral.com/articles/64582/
There have been about 1000 posts on this topic in the past, so search should bring up a few.
October 29, 2009 at 11:22 am
After you take the transaction log backup, issue a CHECKPOINT statement in that database to flush the buffer to disk before you attempt to shrink it. I've had success in the past with a combination of backing up the log and issueing the CHECKPOINT. I believe putting the database in Simple recovery automatically issues the CHECKPOINT for you.
The other thing to look at is your maintenance plans. If you're using the database maintenance plans in SQL Server, they will blindly rebuild indexes whether they need it or not and that can add a lot of unnecessary transactions to the look. Look for a smart index rebuild script (I use Andrew Kellys from SQL Magazine) and test that in development to see if moving that to production will help.
November 9, 2009 at 9:21 am
This weekend,
I took the database full back up.
I took transaction log back up.
Issued the "CHECKPOINT" command and changed the recovery mode to simple.
Shrinked the transaction log.
Changed recovery mode back to full.
Took the full backup of database again.
I was able to free up 100GB of space taken by tdansaction log.
Thanks to all of you for your kind help and support.
November 23, 2009 at 6:05 am
Just curious, do you have a backup plan that executes periodically?
Sonal.
November 23, 2009 at 8:23 am
You need to make sure that you perform regular log backups to ensure that it does not grow so large again. In doing your log backups, you do not need to run a shrink, switch recovery modes, or issue checkpoints. Just run log backups.
November 24, 2009 at 7:27 am
2 thoughts:
Boy, if I had a dollar for every time someone has done this . . . 🙂
There are so many companies out there using SQL Server who have no concept of how to keep their data safe and recoverable . . . just ONE DAY with a consultant would make a WORLD of difference!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 24, 2009 at 9:07 am
sonal_anjaria (11/23/2009)
Just curious, do you have a backup plan that executes periodically?Sonal.
The question is not just a backup plan, but the CORRECT plan.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply