March 16, 2011 at 8:59 pm
Another question related with transaction log.
We have some db backed up weekends full, weekdays differential and transaction log backup.
I still see there is a transaction log very big for one of the database.
I would like to do a shrink log file.
My question is after I do the shrink lof the og file, I guess it will break up the backup log chain.
What should I do for backup after I do a shrink log file in order future restore to be successful?
Thanks
March 17, 2011 at 5:49 am
Shrinking the log file won't affect the log chain. You can do it when you think best.
Shrinking files is a problematic thing to do since it leads to fragmentation. But if you're in a situation where the log grew to large due to a lack of backups or a bad transaction, stuff like that, you can shrink. But, if you find yourself doing it regularly, and by that I mean more than once a quarter on a given database, you might need to reexamine how much log you think you need or the frequency of your log backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2011 at 5:55 am
Shrinking won't break anything, it just releases free space in the log file itself. The best way to do this is to make a log backup immediately before shrinking because the log backup will truncate the log (removing all inactive log records, ie. info about transactions that are no longer running) and so you will have the most amount of free space within the file immediately after a log backup.
However, just shrinking it to the minimum size and leaving it there is rarely a good idea because it will only end up needing to grow again. Repeatedly growing, shrinking and growing files is bad for performance, so you should try to figure out how big your log files "want" to be and then leave them at that size permanently. Usually, a good way to do this is to look at the size of your log backups over time and then set your log file size slightly larger than the largest log backup of the week.
So, you run a log backup, then shrink the log as small as it can get and then manually extend it again to the size you want it to be. No further action is necessary. 🙂
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 17, 2011 at 6:05 am
annasql (3/16/2011)
I still see there is a transaction log very big for one of the database.I would like to do a shrink log file.
Why?
Did the log grow abnormally large due to failed backups?
If there were no unusual operations, no backup failures then the log needs to be that size for the DB operations. Leave it that size, if you shrink it'll just grow back
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
March 17, 2011 at 4:42 pm
Thank you all
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply