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'
- Recover mode for the database was simple.
- sys.dm_tran_database_transactions reported no active transactions (for any database).
- Active log usage for the database (from sys.dm_db_log_stats) was around 800MB.
- 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...