Discussion Locked

Shrink physical size of transaction log

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

5 replies

by nohdowt

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......

by michealirwin

If you used dbcc shrinkfile 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 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

This discussion is closed to new replies.