sumanta.roy52 (5/6/2009)
I have gone through the same topic here and it says take your transaction log backup periodically to truncate the transaction log. But taking back up of transaction log is not reducing the size of the database log file(idf). Please suggest.
In full recovery you have to back the log up on a regular basis. If you don 't, the log file will grow until it fills your hard drive. Please read through this - Managing Transaction Logs[/url]
Backing the log up will not change the size of the log file. It only makes the space inside available for reuse. If you need to reduce the size of the log file, do a once-off shrink file. Do not shrink it down to nothing. You need to work out what size the log needs to be based on the frequency of your log backups and the activity on your database. Once you know that, set the log to that size (plus maybe 10% leeway).
Do not regularly shrink the log. Recommendations are to set it's size and leave it alone. Just make sure you're doing regular log backups.
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