Database Transaction Log Cannot be Shrunk Because it is Marked as “REPLICATION” (and Replication has not been Configured)

  • Article

This week I got involved in a case where a Microsoft Dynamics AX database log could not be reduced in size. In the past, I have found several issues with SQL Server 2005 transaction logs that could not be shrunk due to Ghost Records, this is discussed in the Microsoft KB953991 but in this case the database version was SQL Server 2008.

There are different reasons for a transaction log blocking the shrink process, a list of these reasons can also be found on this MSDN article. Finding the reason for not being able to truncate the transaction log requires using the DMV sys.databases and looking for the information under the log_reuse_wait_desc column. Most of the times the description on this column will be LOG_BACKUP, indicating that a transaction log backup operation is needed in order to truncate the transaction log file. In our case the result from this query was REPLICATION (I am using the AdventureWorks sample database here to illustrate this):

 USE master;
GO
SELECT name, log_reuse_wait_desc, * FROM sys.databases
WHERE name = 'AdventureWorks';

image

After discussing the case with the customer, I found out that a DBCC CHECKDB process was run on this database several weeks ago using the ALLOW_REPAIR_DATA_LOSS repair option. I am not sure these two actions are related but I experienced a similar situation on a SQL Server 2005 case where a database log was marked as REPLICATION after running a DBCC CHECKDB with this REPAIR option.

Trying to restore the status of the transaction log file using the SQL Server Replication stored procedure sp_repldone will not fix the issue as the stored procedure will complain the database is not configured for replication. The fastest and easiest way to get rid of the REPLICATION mark in the transaction log file is to configure Snapshot replication for the database and then, afterwards, removing this configuration from the server.

To configure a Snapshot replication using SQL Server Management Studio you can follow this 10-minutes TechNet video from Ty Anderson. The first half of the video will show you how to configure your SQL Server instance as its own Publisher and Distributor while the second half  shows how to set up the Subscriber. In our case we do not need to configure any subscriber; just remember this when completing the Snapshot Replication wizard:

  • Select the affected database as the database for Replication
  • You do not need to select all the objects for the Replication process, just select any table (one) on the database
  • Remember to create an initial Snapshot when asked to do so during the wizard
  • You can safely use the SQL Server Agent service account in the Security settings as this Replication will only be used temporarily

Once the Snapshot Replication Wizard completes the replication scenario successfully, run again the previous SELECT in sys.database; at this point the ‘log_reuse_wait_desc’ column should show either NOTHING or LOG_BACKUP:

image

If the status is still REPLICATION, execute the following instruction to force all pending transactions to be distributed:

 EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1;

Then we need to remove all replication objects from the database as we do not longer need any Replication for this database. First we drop the Publication (we do not need to drop subscribers firsts as we did not configured any):

 USE AdventureWorks;
GO
EXEC sp_droppublication @publication = N'AW_Test_Publication'

 USE master
GO
EXEC sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'false';

Note that in this example our Publication name is “AW_Test_Publication”. In your case this name will vary depending on what you have specified during the Snapshot Replication Wizard.

Then we need to drop the Distributor:

 USE master;
GO
exec sp_dropdistributor @no_checks = 1;

And make sure, finally, no replication objects remain on the database by running the following stored procedure:

 USE master;
GO
sp_removedbreplication 'AdventureWorks';