SQL Server 2008 SP3 database log file keeps growing and will not shrink

This last week I applied SQL Server 2008 Service Pack 3 (http://support.microsoft.com/kb/2546951) to our OLTP systems. However after a day or so I noticed that log files for one of the database was growing fast and even though transaction log backups were done periodically the size was not reducing. This server was publisher for transaction replication and multiple DBs were replicated to a subscriber. Also server was clustered with one active and one passive node.

When I checked the open transactions by running following on affected database

dbcc opentran

Here is the output of the command

Oldest active transaction:

    SPID (server process ID): 7s

    UID (user ID) : -1

    Name          : tran_sp_MScreate_peer_tables

    LSN           : (94443:31848:1)

    Start time    : May 09 2012  1:01:08:544AM

    SID           : 0x01

Replicated Transaction Information:

        Oldest distributed LSN     : (94753:23165:12)

        Oldest non-distributed LSN : (0:0:0)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now what is this script doing 3 days after the SP3 installation

First see the recovery model of the database in question

select name, recovery_model_desc, log_reuse_wait_desc  from sys.databases where name in ('testdb')

Then check open transactions. You should see the same transaction (tran_sp_MScreate_peer_tables) in results.

select * from sys.dm_tran_active_transactions

What happens is while applying SP3 some upgrade scripts are run for Peer to Peer replication setup (Even though you are not using it will still run)

Resolution:

First check following registry setting on both nodes or single if it is stand alone instance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.InstanceName\Replication\Setup

If value is 0 that indicates that replication upgrade was not completed successfully during SP3 install.

So here is what you should do

1.      Stop all applications from connecting. Yes this is required else deadlock will happen

2.      Run msdb..sp_vupgrade_replication on the server. This will upgrade the replication setup and will run peer table setup script again (Which was the open transaction)

3.      Restart SQL Service.

4.      After restart for some time server will not allow connections and applications will see following error if connecting to the server 'Server is in script upgrade mode. Only administrator can connect at this time. '

5.      After restart wait for few minutes and then check the registry value again. It should change to 1

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.InstanceName\Replication\Setup

After doing this log file size dropped and there were no open transactions