General discussion

Locked

Shrink physical size of transaction log

By scott.vrana ·
I need to shrink the physical size of some SQL transaction log files. Something happened where they grew to 4+ GB in size. The logs have 50mb used space after they were truncated. I've tried using DBCC SHRINKFILE, but it didn't seem to do anything. I know I can re-create new devices, DB and everything else, but was wondering if there is a faster/easier way.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Shrink physical size of transaction log

by nohdowt In reply to Shrink physical size of t ...

The DBCC SHRINKFILE won't immediately shrink the transaction log. You must perform a log backup and enable Truncate log on checkpoint option for it to happen. Because there may be open transactions at the end of the log file, the shrink will occuron the next checkpoint. After things get cleaned up, with the log more up to date, the shrinking will begin.

Hope this helps......

Collapse -

Shrink physical size of transaction log

by scott.vrana In reply to Shrink physical size of t ...

Poster rated this answer

Collapse -

Shrink physical size of transaction log

by michealirwin In reply to Shrink physical size of t ...

If you used dbcc shrinkfile <logfilename> then you just put a shrinkpoint marker into the file. Now you should free the log records by backing up or truncating the log file:

backup log <database> with truncate_only

If it still doesn't shrink, it's because you're at the very end of the file: do a few loggable operations to get the "write point" to the beginning of the log file, do another dbcc shrinkdatabase to mark another shrinkpoint, and do the backup operation again.

You might want to consider autoshrink in future ...

Good luck

Mike

Collapse -

Shrink physical size of transaction log

by scott.vrana In reply to Shrink physical size of t ...

Poster rated this answer

Collapse -

Shrink physical size of transaction log

by scott.vrana In reply to Shrink physical size of t ...

This question was closed by the author

Back to Networks Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums