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 without end. The log appears to not being getting truncated as it should under the simple recovery model.

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

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?

  • Quick question; Is the TLog continuing to grow even now, or did it just have a big growth spurt and stop? Note that once the log grows, it won't normally shrink the file size, regardless what happens to the actual transaction log contained therein. (and it's best -never- to shrink the TLog file so that it never needs to be increased in size) – Andrew Barber Dec 28 '10 at 21:57
  • It continues to grow. – Samantha Branham Dec 28 '10 at 22:31

Run "DBCC OPENTRAN" and it will tell you the spid that has the longest running transaction in the database. Be sure to run it within the context of the database in question.

  • Awesome! This did give me tran_sp_MScreate_peer_tables, but I'm curious about whether or not this is safe to kill. It probably is, but this is a production database so I have to be sure about what I'm doing. – Samantha Branham Dec 28 '10 at 22:33
  • That is part of your peer to peer replication. Are you using replication on that database? If so are there any problems with replication? I would assume that procedure is NOT safe to kill. – mrdenny Dec 28 '10 at 22:39
  • Yes, we have three transactional publications on this server. Yikes. No problems with replication, however. Would a restart be safer? – Samantha Branham Dec 28 '10 at 22:48
  • Restarting the service will just cause the transaction to roll back and start over. Check the wait types for the spid in question and see if it is waiting on something, or if there is some other reason it is running. – mrdenny Dec 28 '10 at 22:56
  • Thanks for your help. When I run dbcc opentran, the spid it gives me is "9s". That seems kind of odd, and I feel completely stuck at this point. – Samantha Branham Dec 29 '10 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.