Recently we had an issue with transactional replication where our publication database transaction log file grew abnormally large. Upon reviewing the transaction log files I noticed that the transaction log file for the publication database contained a large number of unreplicated transactions (i.e. the transactions that affect the objects that are replicated are marked as "For Replication.").

According to SQL Server BOL (http://support.microsoft.com/kb/317375):

“These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them. If an issue with the log-reader task prevents it from reading these transactions in the publisher database, the size of the transaction log may continue to expand as the number of non-replicated transactions increases.”

We had only a few megabytes of free space on the publisher database log file drive, so I decided to reset transactional replication as this is the only option to shrink the publisher database transaction log file.

This article demonstrates the steps which you must follow to truncate the publisher database transaction log file by resetting replication. 

The Process

This is the process to follow. First, connect to the distributor SQL Server via SQL Server Management Studio and expand the SQL Server Agent folder. Open the Job Activity Monitor. Locate and disable/stop the following three replication jobs by right clicking jobs in Job Activity Monitor and then choose disable:

•  <Server-Name>- REPL-Distribution - This job exists on Distributor for push subscriptions or at the Subscriber for pull subscriptions.  In our case it exists at distributor as we are using push subscription. 

• <Server-Name>- REPL-LogReader - This job exists on Distributor and connects to the Publisher.

• <Server-Name>- REPL-QueueReader - This job exists on Distributor and moves changes made at the Subscriber back to the Publisher.

Next click New Query to open a new query window and make a connection to the Publisher SQL Server Instance. Execute the following command at the Publisher on the publication database:

USE [<Published_Database>]
GO 
EXEC [dbo].[sp_repldone]
    @xactid              =       NULL
   ,@xact_segno          =       NULL
   ,@numtrans            =       0
   ,@time                =       0
   ,@reset               =       1

This command will stop database replication until the database is unpublished and republished, which means it will prevent SQL Server from replicating the published database.

Execute the command below to TRUNCATE the transaction log file of publisher database:

USE [master]
GO 
BACKUP LOG [<Published_Database>]
  WITH TRUNCATE_ONLY
Now shrink the transaction log file of published database.

Execute the following store procedure to flush the article cache:

USE [<Published_Database>]
GO 
EXEC [dbo].[sp_replflush]

Execute the TRUNCATE TABLE command against MSrepl_commands table inside distribution database to clear the replicated commands from distributer database:

USE [distribution]
GO 
TRUNCATE TABLE [dbo].[MSrepl_commands]

Launch the Replication Monitor by right clicking Replication folder and choosing Launch Replication Monitor from the menu:

Expand My Publishers > <Publisher_Server> in Replication Monitor. Right-click your publication and then choose Reinitialize All Subscriptions:

In Reinitialize Subscription(s) dialog box tick Generate the new snap now option and then click Mark For Reinitialization button:

Enable and start the following three replication jobs which has been disabled in this procedure:

•  <Server-Name>- REPL-Distribution - This job exists on Distributor for push subscriptions or at the Subscriber for pull subscriptions.  In our case it exists at distributor as we are using push subscription. 

• <Server-Name>- REPL-LogReader - This job exists on Distributor and connects to the Publisher.

• <Server-Name>- REPL-QueueReader - This job exists on Distributor and moves changes made at the Subscriber back to the Publisher.

You can now launch Replication Monitor once again to view the status of your transactional replication which should now be in healthy state.

Conclusion

There may be times when the transaction log size of the publisher database can expand if you are using replication. This article demonstrated the steps which you must follow to gracefully truncate the publisher database transaction log file by resetting replication.