Is shrinking a transaction log OK to do on a live database?

Question

In Microsoft SQL Server 2005, is it OK to shrink the log file of an online database, or will it cause an interruption of service?

Answer 1

Shrinkfile will not take your database offline.

Answer 2

DBCC SHRINKFILE itself does not make the database go offline. However, simply executing this command will not necessarily reduce the size on disk of the log, which depends on when the last full (for SIMPLE recovery types) or t-log (for FULL recovery type) backup was taken. If there's not been a recent backup of the logs then there'll be no log space to reclaim.

Finally, unless there has been some unforeseen, explosive growth due to an abnormally large write operation/batch, you may want to reconsider shrinking the file because the database is showing a need for a log file that large and the server needs to dedicate resources to expanding it back out. Perhaps adding storage would be a reasonable alternative.

Answer 3

Yes, it's fine. It doesn't affect any existing transactions, nor does it move any data around like database shrinking. Don't shrink it right back though, because growing a log file takes effort.