On our production db (~117gb) we quite a few tables being replicated. Historically our maintenance plan has kept the transaction log fairly small. Since we installed SP3, the transaction log is growing pretty quickly (currently ~213gb).

We made sure the install was successful by checking the registry entries, but not too sure what else to check. Searching hasn't found any similar problems with SP3 as the culprit.

Not too sure how to look at the transaction log either since DBCC LOG results are cryptic at best.

This is output from version:

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   
Sep 21 2011 22:45:45   
Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) 
   on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Full Recovery Mode = true Backing up transaction log = true on a daily basis.

This is a production database that has had the transaction log maintained to a small size for many years without a problem... until SP3 came along.

I ran the DBCC OpenTran and found this:

Oldest active transaction: SPID (server process ID): 9s UID (user ID) : -1 Name : tran_sp_MScreate_peer_tables LSN : (365893:25399:1) Start time : Nov 12 2011 8:26:10:927AM SID : 0x01 Replicated Transaction Information: Oldest distributed LSN : (366831:664081:8) Oldest non-distributed LSN : (0:0:0)

Quite a few other people have reported this same long running process after a service pack install... hmmmm.

Not too sure what to do about it. Might consider pausing or killing my replication jobs and see if it goes away. All indications are that since it is a '9s' SPID, the system owns it and it can't be killed.

thoughts on how to proceed?

  • 1
    as an update, we found this microsoft post (support.microsoft.com/kb/2509302): If the instance of SQL Server is a stand-alone instance, you can resolve the issue by stopping both SQL Server and the SQL Server Agent service, disabling the SQL Server Agent service, and then restarting just the SQL Server service. This lets the upgrade process complete in the database. After this process is complete, you can restart the SQL Server Agent service. Will be trying this tonight and will report back.
    – Dan Ribar
    Nov 28, 2011 at 21:02
  • 1
    So this morning our log file is down into the mb range... down from 213gb. WOW. We followed these simple directions above and then ran the full backup job a couple of times and the problem was resolved. It all came back to the install of SP3 never got done... I mean REALLY done. Thanks everyone for the input. This is a great site.
    – Dan Ribar
    Nov 29, 2011 at 13:24

4 Answers 4


I'd suggest the 2 are unrelated.

Has anything changed:

  • in the maintenance plan setting
  • recovery model of the database (is it now FULL where it was SIMPLE)
  • did you take log backups before (FULL) in the maintenance plan? Do you still do them?
  • did you have auto shrink in the maintenance plan? This is bad anyway
  • have you any open transaction that is preventing a log backup (or shrink)

Also, restore a database from pre-SP3 on another server and compare the sys.databases entries


A good start would be to look at the log_reuse_wait_desc column in sys.databases for this database. This will tell you why the log is not being truncated. The following BOL article will help you interpret the results: http://msdn.microsoft.com/en-us/library/ms345414.aspx.


It sounds like your database is in Full Recovery mode. Can you verify this?

Are you backing up your transaction log? If it is growing, I'm guessing not. Check if your maintenance plan is actually doing what you think it is. There are SQL Agent jobs associated with this. You can view the History of your Log backup job to see if it is actually successful. I'm guessing it is not, otherwise your log file would get truncated when you back it up and it wouldn't be growing.

Also, what is the interval that you are backing up your transaction log? If it is not often enough, then it will grow. There is a happy medium between doing it too often and too infrequently.


It's possible that there is a long-running transaction that is prevent the log from being marked for reuse. The suggestion by @Ben Thul would be a good place to start. If the log_reuse_wait_desc field is ACTIVE_TRANSACTION, you can examine active transactions using the following query.

SELECT  at.transaction_id, 
FROM    sys.dm_tran_active_transactions at
        LEFT JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
ORDER BY transaction_begin_time

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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