Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Determine data latency between Publisher and Subscriber in SQL Server Transactional Replication


By:   |   Last Updated: 2012-01-26   |   Comments (16)   |   Related Tips: More > Replication

Problem

I have transactional replication configured in my production environment. It appears the subscriber data is quite far behind from the publisher database. What are the ways we can identify how far behind the subscriber is for replication?

Solution

To find out what still needs to be replicated, we could use both Replication Monitor as well as T-SQL commands to find out how what needs to be replicated to the subscriber database. Both options assume transactional replication is already configured in your environment.

Option 1: Using Replication Monitor

In SQL Server Management Studio (SSMS), navigate to Replication and right click and select 'Launch Replication Monitor'. Go to your listed server and expand it. Click on your publication and on the right side pane under the 'All Subscriptions' tab, go to your subscription and double click on it. Once done, you would see this window.

Replication status as viewed on Replication Monitor

From this window, you could see the details of the activities that are occurring between the 'publisher-distributor' and 'distributor-subscriber' combinations. Click on the respective tabs to check the status of replication. If there are any replication commands that remain to be applied at the subscriber from the distribution database you would see the details in the 'Undistributed commands'. From this tab, you would get an idea of estimated time remaining to apply commands at the subscriber. Below is a sample screenshot.

Undistributed commands  as viewed from Replication Monitor

Replication Monitor gives you this simple interface to identify any issues you may face with your replication configuration. For more details related to replication latency, we could use some replication commands as shown below.

Option 2: Using Replication commands

Run this on publisher database

To check if replication is fine, we could run sp_repltrans on the publisher database. This displays the undistributed commands present in the publisher database. If your log reader agent is scheduled to run continuously and if this command returns no rows, replication is fine on the publisher side. However, if your log reader agent is scheduled to run at intervals and there are changes that need to be sent to the distribution database, you would see rows returned when you execute (during the interval) this procedure which shows the LSNs of the transactions. See sample screenshot below.

Output of  sp_repltrans when run on publisher database

On a busy server, if the log reader agent is running continuously, the rows that are returned after executing sp_repltrans would be constantly changing based on the load. By using this procedure, you wouldn't get the exact command that is pending but you would get an idea on the progress that is happening in your replication environment.

Run this on distribution database

The distribution database contains the system tables - MSrepl_commands and MSrepl_transactions which contain details of the replicated commands. Here is a sample output of a select query on these system tables.

select * from distribution.dbo.MSrepl_commands
select * from distribution.dbo.MSrepl_transactions

Select operation on MSrepl_commands and MSrepl_transactions

From the screenshot, we could see that the command column is of varbinary datatype and is non-readable. Hence, we could execute the sp_browsereplcmds system procedure in the distribution database with the relevant parameters and retrieve the data of the replicated commands in a user friendly manner.

In the above example, if we need to find out the actual command corresponding to xact_seqno = '0x00000085000002A10003' and command_id = 1, we could execute sp_browsereplcmds with these parameters. See screenshot and sample script below.

Use distribution
exec sp_browsereplcmds @xact_seqno_start = '0x00000085000002A10003',
    @xact_seqno_end = '0x00000085000002A10003',
    @publisher_database_id = '1', -- run sp_helppublication on publisher database             
                       @command_id = '1' -- command_id in MSrepl_commands table distribution database

A sample execution of sp_browsereplcmds on 
distribution database

Here, we could see the actual command that was executed which was an insert statement. It is recommended to execute this procedure with the appropriate parameters otherwise it could result in generation of a huge number of rows as output.

In option 1, using replication monitor, we saw how to view the 'undistributed commands'. The same data could be obtained by executing sp_replmonitorsubscriptionpendingcmds in the distribution database. Use sample script below.

sp_replmonitorsubscriptionpendingcmds  
 @publisher ='Enter publisher server name',
 @publisher_db = 'Enter publisher database name',
 @publication ='Enter name of publication',
 @subscriber ='Enter subscriber server name',
 @subscriber_db ='Enter subscriber database name',
 @subscription_type ='0' --0 for push and 1 for pull 

Below is a sample output.

Sample output of executing sp_replmonitorsubscriptionpendingcmds

Testing

In your test replication environment, you could stop the distribution agent job and run a few insert commands in the publisher database. Once done, execute this procedure sp_replmonitorsubscriptionpendingcmds in the distribution database to find out the details of the pending commands to be applied on to the subscriber. Then proceed to query the MSrepl_commands and MSrepl_transactions system tables in the distribution database to determine the actual commands that are yet to be sent to the subscriber. Using MSrepl_transactions system table you could get the time you had executed the insert statements in your publisher database. Then make use of the sp_browsereplcmds as shown above to find out the full text of commands by providing the appropriate parameters. This would give an idea of the actual commands that are yet to be replicated to the subscriber.

You can also use this script to query the distribution system tables.

select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time 
  from MSrepl_commands rc, MSrepl_transactions rt
   where rc.xact_seqno = rt.xact_seqno

 

It should be noted that the data available in MSrepl_commands, MSrepl_transactions, sp_browsereplcmds is purged periodically based on the schedule of the distribution clean up job and the distribution retention period.

Next Steps


Last Updated: 2012-01-26


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, January 11, 2019 - 2:34:07 AM - udhayaganesh Pachiyappan Back To Top

Let us know how to find transaction completed time on subscription.


Friday, November 24, 2017 - 6:26:55 AM - Rimjhim Back To Top

 

when we run the sp_replmonitorsubscriptionpendingcmds
 
the estimatedprocess tme is that in seconds? 

 


Wednesday, October 12, 2016 - 4:29:26 PM - Bojanna Muruvanda Back To Top

 This article helped me. Thanks much Moinu.

 


Saturday, September 27, 2014 - 2:09:50 PM - nishant Back To Top

Thanks for the good work


Thursday, July 18, 2013 - 2:48:24 PM - Mohammed Moinudheen Back To Top

Just have a look at this link and the related comments.

http://sqlblog.com/blogs/linchi_shea/archive/2009/06/05/distribution-latency-in-transactional-replication-is-a-volume-surge-the-culprit.aspx

It is quite useful to understand the issue you are facing.


Wednesday, July 17, 2013 - 2:52:32 PM - Mahesh Back To Top

Thanks for a nice detailed article.

I have transactional replication configured in SQL 2008R2 EE. It is working very good still yesterday. But today, I could see some transactions like 20 to 30 taking 3 to 4 hours of time to replicate from Distributer to Subscriber. Earlier, it took only secs to replicate. 

Appreciate your help on this. Thank you.

Note : Network bandwidth is very good.


Monday, March 25, 2013 - 9:22:11 PM - kamaraju Back To Top

Very nice tips sir.......... thanks for helping us


Sunday, September 16, 2012 - 1:35:05 AM - Yobo Back To Top

Hi Mohd, Thank you! This is a helpful article.


Friday, May 18, 2012 - 8:02:54 AM - D P Tripathy Back To Top

Thanks for this tips.... useful to all DBA


Thursday, May 17, 2012 - 12:16:55 PM - Srinath Back To Top

Handsomely written.....thanks for this tip..it really helped me a lot...Keep up this good work Moinu....


Tuesday, February 07, 2012 - 3:23:23 AM - Mohammed Moinudheen Back To Top

@Eric: Hope you are giving the right parameters as described. Also, no need to stop the log reader agent. I was trying to show the undistributed commands in the distribution database as described under the 'Testing' section of the tip.


Monday, February 06, 2012 - 6:54:55 PM - Eric Back To Top

I when I pulled the transaction log seqno from the initial transaction and applied it to the sp_browsereplcmds it returned no records.  Now I stopped the logreader, and distribution agent services just to make sure the transactions didn't get applied.


Friday, January 27, 2012 - 4:55:38 AM - Mohammed Moinudheen Back To Top

@Ron, Nataraja: Thank you for sharing the scripts

@andrismg : Thank you for your kind words. Regarding your question, it is good, I thought I could work on another tip on this. Will check on feasibility of this and let you know.

 


Thursday, January 26, 2012 - 1:42:35 PM - Nataraja Sidgal Back To Top

-- Instead of using the SSMS to monitor replication, use this script (run on distribution database) and will give the info for all the articles in the publication/database

select 

article, t1.*

from distribution.dbo.MSdistribution_status t1 join distribution.dbo.MSarticles t2 on t1.article_id = t2.article_id

where UndelivCmdsInDistDB > 0


Thursday, January 26, 2012 - 1:29:39 PM - andrismg Back To Top

Hi Mohammed excellent post very helpful.

I very very happy with this post.... I'm newbie and for me is excellent this post. Now i would like know how can send alert and notification when the publisher synchronized with the subscribers in SQL Server Transaction Replication.

Fron Santo Domingo, Dominican Republic

Thanks a Millions Mohammed.


Thursday, January 26, 2012 - 10:09:57 AM - Ron Baxter Back To Top

 


--****** THis will generate a list of update statistic commands for tables above the
--****** Having Count(*) number listed at the bottom of the code and the number of commands
---***** waiting for each table in replication for the Database listed.

SET NOCOUNT ON
Select 'UPDATE STATISTICS ' + rtrim(MSarticles.Publisher_DB) + '.dbo.' + rtrim(MSarticles.article) + '
GO  ---------------------------->   ' + cast(Count(*) as varchar(15)) + ' Commands Pending'
From MSrepl_commands
JOIN MSpublisher_databases ON MSpublisher_databases.id = MSrepl_commands.publisher_database_id
JOIN MSarticles ON MSrepl_commands.article_id = MSarticles.article_id
and MSpublisher_databases.publisher_id = MSarticles.publisher_id
and MSpublisher_databases.Publisher_db =MSarticles.Publisher_db
JOIN MSpublications ON MSarticles.publisher_id = MSpublications.publisher_id
and MSarticles.publication_id   = MSpublications.publication_id
--------------------------------------------------------------------------------
Where  MSpublisher_databases.Publisher_DB = 'YOUR_DB_NAME'
Group By MSarticles.Publisher_DB, MSarticles.article
Having Count(*) > 10 -- to list tables with more than 10 commands pending
Order By Count(*) desc

 

 


Learn more about SQL Server tools