SQL Server log_reuse_wait_desc is ACTIVE_TRANSACTION but there are no active transactions

Question

Just had an issue with a SQL Server 2016 database where this query returned ACTIVE_TRANSACTION for log_reuse_wait_desc:

select name,log_reuse_wait_desc from sys.databases where name='database name'

1. Recover mode for the database was simple.
2. sys.dm_tran_database_transactions reported no active transactions (for any database).
3. Active log usage for the database (from sys.dm_db_log_stats) was around 800MB.
4. No backup was run during the period in question.

This state persisted for 18 hours until this was run:

DBCC SHRINKFILE (N'database log file' , 0, TRUNCATEONLY)

That cleared the ACTIVE_TRANSACTION state and the active log usage dropped to a fraction of a MB.

Is there some minimum amount of log that needs to be used before the log gets cleared? I'm not seeing this issue on a number of other servers with the same configuration...

Answer 1

The log can't be reused until a database checkpoint writes all the dirty pages to the data files, and automatic checkpoints don't happen on a fixed schedule:

The time interval between automatic checkpoints can be highly variable. A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations. Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

Database Checkpoints - Automatic Checkpoint

Hannah Vernon explains this behavior in more detail here:

a single checkpoint will be issued when the log becomes 70 % full. Once this checkpoint has been issued, further checkpoints may not be issued even thought the log is over 70% full, even if the automatic checkpoint resulted in no log truncation activity. However, if further transactions are generated, and the database engine believes the recovery time will be longer than the configured recovery interval, SQL Server will issue automatic checkpoints frequently as long as transactions are being generated. If no transactions are being generated, no automatic checkpoints will be issued.

CHECKPOINT and Simple Recovery Model

So your

DBCC SHRINKFILE (N'database log file' , 0, TRUNCATEONLY)

either directly or indirectly caused a checkpoint.