Huge transaction log with SQL Server database in simple recovery mode

It means you once had a single transaction that lasted for so long that it forced the log to grow 410GB. The log cannot be reused if there is an active transaction since the rollback information cannot be erased. Such an example would be if someone open an SSMS query, starts a transaction, updates a record and then goes in vacation. The transaction will be active and force the log to grow until is eventually committed or rolled back. When the transaction eventually ends the used space can finally be reclaimed, leaving a huge empty log file.

Another scenario is if you had about 200GB of data updated in a single transaction. The log will store the before and after images of the changes thus consuming twice the space, and it cannot be reused, again because is all one single transaction.

Update

I neglected to mention Replication that is also a factor that can prevent log truncation. And so is Mirroring, an distributed transaction (technically that is the same as an 'active transaction', but the DTC implication makes it a distinct case). The complete list and explanations is at Factors That Can Delay Log Truncation.


You're missing an argument in dbcc shrinkfile:

dbcc shrinkfile (MyDatabase_log, 20000, TRUNCATEONLY)

NOTRUNCATE is the default, which moves allocated blocks to the beginning of unallocated space. TRUNCATEONLY removes the unallocated space. So if you do a NOTRUNCATE followed by a TRUNCATEONLY, you get one slimmed down log.

Tags:

Sql Server