How do I identify and fix the cause of transaction log growth on SIMPLE recovery model databases?
- by Stuart B
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?
