Find undistributed replication command in SQL Server

Find undistributed replication command in SQL Server

I have written one article to find replication latency sometime back. If I find latency of 10 seconds or more for longer time then I always try to find the bottleneck and fix it because in the environment I am working, won’t afford big latency.

In finding the bottleneck, my first step would be to find how many commands are pending to apply in replicated database. To find undistributed command, you can use replication monitor which comes with SQL Server itself or use sp_replmonitorsubscriptionpendingcmds” system stored procedure which you can find in distributor database of replication.

 

[sourcecode language=”sql”]
–system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
–replication publisher server
@publisher =’PubServer’,
–replication publisher database
@publisher_db = ‘AdventureWorks2012′,
–replication publication name
@publication =’AdventureWorks2012Pub’,
–replication subscriber server
@subscriber =’SubServer’,
–replication subscriber database
@subscriber_db =’AdventureWorks2012ReplDB’,
–choose type of subscription you have
@subscription_type =’1′ –0 for push and 1 for pull
GO
[/sourcecode]

 

Here is the screen capture of output.

ReplicationPendingCommand

This screen capture shows that total number of 64 commands are waiting to apply from “AdventureWorks2012” database to “AdventureWorks2012ReplDB” and that would take 0 (<1) seconds.

I have written few more replication related articles, have a look if you are interested.

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

4 thoughts on “Find undistributed replication command in SQL Server”

  1. Hi Ritesh,
    Thanks for writing such an informative article..We have a big replicated environment and I am trying to cleanup some as we think some articles might not be replicating data….Is there anyway to find a list of articles that are no more publishing the data?? Thanks in Advance….Minesh

    1. Hello Minesh,

      First of all you have to check your publication whether articles are there or not. if it is there then you can count number of rows in source table and destination table.

Comments are closed.