Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Process to truncate transaction log of replicated database

By Basit Aalishan Masood-Al-Farooq,

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.

Total article views: 5490 | Views in the last 30 days: 35
 
Related Articles
ARTICLE

Transaction Replication Publisher failover to Mirror

Transaction Replication Publisher failover/failback to mirror standby with automatic redirection of ...

FORUM

Database mirroring with transactional replication

Database mirroring with transactional replication

SCRIPT

Find Transactional Replication Publisher

Finds the publisher, publisher database, and publication that your subscription table is replicated ...

FORUM

transactional replication

transactional replication

FORUM

Database mirroring with transactional replication

Database mirroring with transactional replication

Tags
replication    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones