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.
--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
Here is the screen capture of output.
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)
Reference: Ritesh Shah
Sharing IT tips at “Quick Learn“
Note: Microsoft Books online is a default reference of all articles.