We're having periodic issues with the ReportServerTempDB gradually growing until it threatens to fill the drive. It does not respond to a simple DBCC SHRINKFILE command.
select * from sys.databases where name = 'ReportServerTempDB'
Shows the log_reuse_wait and desc to be 4, ACTIVE_TRANSACTION. A BOL article, "Managing the Transaction Log" describes this as:
A transaction is active (all recovery models).
A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. For more information, see "Long-Running Active Transactions," later in this topic.
A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions.
We have the recovery model set to Simple, so there are no log backups. Also, the log file is steadily growing over several full backup jobs, so it must be a Deferred Transaction. The solution suggested by BOL works - we turn off all reporting the application, turn off SQL Reporting Services, take the ReportServer & ReportServerTempDB off & online, then the DBCC SHRINKFILE command works.
But I would like to discover the cause of the deferred transaction and prevent this from happening in the first place. Is there a way to query the DB and identify source of the deferred transaction, the report it originates in, or information about the I/O error BOL suggests might cause the deferred transaction?