Trimming HUGE Transaction Log

#91

On Tue, 14 Oct 2008 08:44:01 -0700, Always OpenTo Suggestions
<AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

Quote:

That is exactly what I needed to know. My log backup file has been trimmed
to less than 1% of the original size. The only problem left is the actual
log file (.ldf). It is still about 10 gig for a 5 gig database.

This is a common problem when logs backups have been neglected.

Quote:

The only
way I've found to reduce that is to change to Recovery Model to SIMPLE,
backup everything then change the REcovery Model Back to FULL.

That will not change the size of the .ldf file. That will change the
size of the log data inside the log file, but the log file size will
not change.

Quote:

So-o-o-o-o!
Is it best to leave the .LDF @ 10 gig or reduce it. Note - I did reduce it
then backed up the transaction log a few more times and was able to restore
the database to a point-in-time.

To reduce the size of the log file you have to use DBCC SHRINKFILE.
Note that this is not something that should be part of any regular
maintenance. It is a special fix for a problem that should not recur
if the logs are managed properly. Please read this article closely
before shrinking:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Roy Harvey
Beacon Falls, CT

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Trimming HUGE Transaction Log

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools

Re: Trimming HUGE Transaction Log - 10-14-2008 , 12:01 PM