Sql-server – SQL Server 2008 transaction log growing since SP3 Installed

sql serversql-server-2008

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?

Best Answer

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

Related Question