Shrink Log File without breaking Log Chain

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?

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

• Make a log backup. This marks the active log space for reuse.

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

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

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

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

StackExchange Database Administrators Q#13352, answer score: 3