I was investigating the rapid growth of a SQL Server 2005 transaction log when I found that transaction logs will only truncate correctly – if the sys.databases "log_reuse_wait" column is set to 0 – meaning that nothing is keeping the transaction log from reusing existing space.
One day when I was intending to backup/truncate a log file, I found that this column had a 4, or ACTIVE_TRANSACTION going on in the tempdb. I then checked for any open transactions using DBCC OPENTRAN('tempdb'), and the open_tran column from sysprocesses. The result was that I could find no active transactions anywhere in the system.
Are the settings in the log_reuse_wait column accurate? Are there transactions going on that are not detectable using the methods I described above? Am I just missing something obvious?