Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
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?

share|improve this question
    
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. –  Stuart Branham Dec 28 '10 at 22:31
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  Stuart 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? –  Stuart 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. –  Stuart Branham Dec 29 '10 at 23:06
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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