I still don’t know why I was seeing the ACTIVE_TRANSACTION in the sys.databases log_reuse_wait_desc column – when there were no transactions running, but my subsequent experience indicates that the log_reuse_wait column for the tempdb changes for reasons that are not very clear, and for my purposes, not very relevant. Also, I found that running DBCC OPENTRAN, or the “select open_tran from sysprocess” code, is a lot less informative than using the below statements when looking for transaction information:
select * from sys.dm_tran_active_transactions select * from sys.dm_tran_session_transactions select * from sys.dm_tran_locks
Here there are explanations how log_reuse_wait_desc is working:
We also need to understand how the log_reuse_wait_desc reporting mechanism works. It gives the reason why log truncation couldn’t happen the last time log truncation was attempted. This can be confusing – for instance if you see ACTIVE_BACKUP_OR_RESTORE and you know there isn’t a backup or restore operation running, this just means that there was one running the last time log truncation was attempted.
So in your case there is no ACTIVE TRANSACTION right now, but it was when log truncation was attempted last time.
There are a couple of links to additional tools/references you can use to help troubleshoot this problem on the References link for this video:
Managing SQL Server 2005 and 2008 Log Files
That said, the information in log_reuse_wait should be accurate. You likely just had a stalled or orphaned transaction that you weren’t somehow able to spot.