Insert Tracer Token to see replication latency
I have written article to find replication latency at “Find latency in transactional replication”. After reading this article, one of the blog reader put comment in article that they have transactional replication setup and if he runs the query given in my blog, he doesn’t get anything.
Well, let me tell you that replication latency is being maintained neither in publisher database nor in subscriber database. Latency is being maintained in “Distribution” database so the query I have provided at “Find latency in transactional replication”, should be run in “Distribution” database only.
Query given in article respond only if “Tracer Token” is inserted for the publication. You can insert “Tracer Token” from “Replication Monitor” but it won’t give you current status. You have to keep inserting tracer token and I used to do it from a SQL Server job which executes at every 5 minutes and insert new tracer token so that I can get latest latency.
Here is the script which inserts tracer token from the script and that needs to call from SQL Server Job.
DECLARE @PublicationName VARCHAR(50) --give your own publication name here. --my publication name is "AdventureWorks2012" SET @PublicationName='AdventureWorks2012' exec sys.sp_posttracertoken @publication =@PublicationName GO
Execute this TSQL code in your publication database and enjoy the script given in “Find latency in transactional replication”.
If you are interested to read other replication related articles, have a look at following list:
- 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)
Reference: Ritesh Shah
Sharing IT tips at “Quick Learn“
Note: Microsoft Books online is a default reference of all articles.