The image below shows a transaction replication problem that I am currently investigating.
The image comes from the Replication Monitor.
How can I get this information using T-SQL?
merge-replicationreplicationsql serversql-server-2005transactional-replication
Best Answer
After doing some online search,I found what you needed,hope my answer helps you.
Answer 1
Note:
T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions.
Things to be considered before executing the below script
Requires permission on the following tables inside distribution and master databases
Answer 2
Script_2 found on MSDN
Answer 3
Script featured on SimpleTalk TSQL as an agent job.
Step 1
Create in a DBA database installed on the subscriber server. The code to create the table is:
Script 1
The data in this table is populated by the monitoring procedures and provides an historical context for examining issues. But to monitor what is happening right now more is required.
There are three things that help to determine the health of replication.
The number of outstanding commands that are pending for the subscription
To check the status of the distribution jobs
Run the Microsoft-supplied procedure
sp_replmonitorsubscriptionpendingcmds
The code below requires the Ad Hoc Distributed Queries server configuration option be enabled. Here I create the email to be sent assuming the previous Script 3 found an issue
sp_replmonitorsubscriptionpendingcmds
.Last process is to periodically delete rows from the replication status table so the data does not get stale
Pls also consider I also noted that There is an issue with sp_replmonitorsubscriptionpendingcmds if you are still with SQL Server 2005 and 2008 Microsoft Connect
If any of the above answer are unclear you can follow the source I have provided
Thanks!