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?