January 21, 2009 at 10:33 am
Hi,
One of our databases had its logfile grow to 13GB.
On scruitiny we found that it had not been backed up for a while.
I immediately took a log backup. The backup was successful.
But now when I am trying to shrink the log file using DBCC SHRINKFILE it doesn't shrink.
As per my understanding transaction log cannot be shrunk unless it is truncated and log truncation happens when log backup is done. So why it is not shrinking now?
There are no open transactions.
Pls advise.
January 21, 2009 at 10:38 am
Try going a full backup and then try to shrink the log again. If it still doesn't shrink. See http://www.sqlservercentral.com/scripts/63782/ for a script to force it. I have used this script (or a variation) with success when I have encountered a log that won't shrink.
Francis
January 21, 2009 at 10:46 am
You probably cannot shrink the log because the active portion of the log file is near the end. You need to setup and run frequent transaction log backups (every hour??).
After running several transaction log backups, the active portion will again be at the beginning and you will be able to shrink the file (one time operation, because you now have frequent transaction log backups running - it won't grow to that large size again :))
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 21, 2009 at 10:53 am
Thanks for your replies.
I forgot to mention that before taking the log backup the used space in T-Log was 12.9 GB out of 13 GB.
After taking the log backup the used space is just 50 MB out of 13 GB.
Still wondering why it is not shrinking.
I will try your suggestions too.
Thanks !
January 21, 2009 at 10:55 am
Hi
What is the database recovery model ???
January 21, 2009 at 10:58 am
CrazyMan may have a point. If you change the recovery model to simple, you may be able to shrink the log. Then you can change the recovery model to FULL again. Note this will wreck your chain of backups, so do a transaction log backup first, then after you are done do a full backup. Personnally I prefer to just use the script I mentioned in my earlier post.
Francis
January 21, 2009 at 11:14 am
The database is in FULL recovery model.
I took a look at the script mentioned by you.
It is using BACKUP LOG WITH TRUNCATE_ONLY.
I do not want to use the TRUNCATE_ONLY option. That would be my last option.
Unable to understand if the used space is just 50 MB in the logfile why on earth it wouldn't shrink. What is preventing it? :crazy:
January 21, 2009 at 11:49 am
As you have done already, make sure you've backed up the log file. SQL Server doesn't want to truncate a log file that's not been backed up, and this is the behaviour we would want. So, the first step is to backup the log file, even if you just toss the backup.
Run the command DBCC LOGINFO against the desired database and look for entries with the Status=2. A Status of 2 indicates that this portion of the log is active. SQL Server cannot truncate a log before this active portion. If the active portion is at or near the end of the log file, this will prevent the log from being shrunk.
As with all things, the natural way is best. As Jeffery Williams mentioned in an earlier post, once you've backed up the transaction log, future transactions will eventually force the active portion to wrap around to the beginning of the file. When that happens, the log can be shrunk. If you can't wait, use the script which works fine. I've used it before. Backup the database both before and after running this script.
Francis
January 21, 2009 at 11:49 am
As mentioned earlier, the active portion of your transaction log may be at the end of the log file. If that is the case, you won't be able to shrink the log at this time. Continue running periodic t-log backups, as suggested every hour or so. Keep trying to shrink the log after each t-log backup has processed. Once the active portion of the log is at the beginning of the t-log file, you will be able to shrink it.
January 21, 2009 at 1:10 pm
Gail, From the original post, it looks like it was allowed to grow due to a lack of backups being taken on the log file. If they start running scheduled t-log backups, it may not need to be as large as it currently is at about 13 GB. With regularly scheduled backups it may only need to be a tenth of that.
Guessing based on the posts.
January 21, 2009 at 11:22 pm
Singh,
What is the reason you are not running backup log with truncate_only.
If you run the backup log with truncate_only, I am sure your log file will be shrinked.
Rajesh Kasturi
January 22, 2009 at 12:08 am
Rajesh kasturi (1/21/2009)
What is the reason you are not running backup log with truncate_only.If you run the backup log with truncate_only, I am sure your log file will be shrinked.
Perhaps because he doesn't want to break his log chain and have to do a full backup immediately thereafter.
Lynn: Sorry, I noticed the reason straight after posting and hence deleted the post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 4:58 am
I took two more log backups at intervals of 8 hours and again tried to shrink.
Voila ! It worked. The logfile is just 500 MB now.
Thanks to everyone who replied.
Regards
January 22, 2009 at 5:57 am
WOW it is good to hear from you.
Nice Thread.
Rajesh Kasturi
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply