SQL Server 2008 transaction log growing since SP3 Installed

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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, 
        at.transaction_begin_time, 
        st.session_id,
        st.is_user_transaction
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