Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '11 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 '11 at 13:24
add comment

migrated from stackoverflow.com Nov 28 '11 at 17:03

This question came from our site for professional and enthusiast programmers.

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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
share|improve this answer
add 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.