I recently upgraded our SQL Server 2008 installations to service pack 2. One of our databases is on the simple recovery model, but its transaction log is growing extremely fast.

The path I'm currently investigating is that we have a transaction somewhere out there stuck in active state. Here is why:

select name, recovery_model_desc, log_reuse_wait_desc  from sys.databases where name in ('SimpleDB')
name     recovery_model_desc log_reuse_wait_desc
SimpleDB SIMPLE              ACTIVE_TRANSACTION

When I check my active transactions, I get the following. Note that I installed SP2 and restarted our server on 12/25 at around noonish.

select transaction_id, name, transaction_begin_time, transaction_type from sys.dm_tran_active_transactions
transaction_id name                         transaction_begin_time   transaction_type
233            worktable                    2010-12-25 12:44:29.283  2
236            worktable                    2010-12-25 12:44:29.283  2
238            worktable                    2010-12-25 12:44:29.283  2
240            worktable                    2010-12-25 12:44:29.283  2
243            worktable                    2010-12-25 12:44:29.283  2
245            worktable                    2010-12-25 12:44:29.283  2
62210          tran_sp_MScreate_peer_tables 2010-12-25 12:45:00.880  1
55422856       user_transaction             2010-12-28 16:41:56.703  1
55422889       SELECT                       2010-12-28 16:41:57.303  2
470            LobStorageProviderSession    2010-12-25 12:44:30.510  2

Note that according to the documentation a transaction_type of 1 means read/write, and 2 means read-only.

So, my line of thinking is that the trans_sp_MScreate_peer_tables transaction is stuck for some reason and holding up transaction log truncation. Is this a plausible scenario? Correct me if my line of thinking is off, as I'm not a SQL Server expert. If this is correct, how do I erase that transaction so that my transaction log is truncated as usual?