Shrink Log File without breaking Log Chain

Question

What is the best way to shrink a log file without breaking the log chain? I realize you can do something like:

alter database yourDb
set recovery simple
go

dbcc shrinkfile(LogFileName, 0)
go

alter database yourDb
set recovery full
go

But I really don't want to break the log chain. Is there any way to accomplish this?

Answer 1

According to the comment on this answer "Shrinking databases makes God kill kittens" :-). So I'd advise you not do it.

Anyway, I don't have another solution to your problem except to backup the log, then shrink it. Switching to simple recovery model will break the chain. There isn't a best way to shrink the log file. Except not use FULL RECOVERY if it's not necessary, so shrinking of log won't be needed.

Edit: Have you seen what's using your transaction log? Maybe there's a long running open transaction. Or the need for a backup..a checkpoint.. Check this article for some ideas why your log file might be so big.

Answer 2

I vaguely recall you have to do something like this, since the SQL Server log file is written cyclically.

1. Make a log backup. This marks the active log space for reuse.
2. Shrink the log file. This will shrink it down to the end of the last virtual log file in use (which will probably leave it larger than what you wanted to shrink it to).
3. Since the end of the log file will now be in use, given that's where it stopped shrinking due to a VLF being in use, SQL Server will wrap back to the beginning of the log file for further log writes.
4. CHECKPOINT to make sure dirty pages for active transactions are flushed, then make a backup again. (The CHECKPOINT might not be necessary - you can try without first.)
5. Shrink the log file again. You should be able to bring it down to where you want.

If that still doesn't work, check DBCC OPENTRAN, and the log_reuse_wait column in master.sys.databases.