how to monitoring SQL merge replication

You seem to be asking open ended questions without providing context.
Scripting is a broad term.
Monitoring is as well.
You can use SQL agent job to run the two SPs. Since you want monitoring, the SQL agent job needs to notify either after the result of running the sp for the publisher, the subscriber, etc. is hitting a specific threshold.

You can use it to establish a baseline of current replication data transfer.

The question is how do you want to ve notified about it?
If not using SQL agent job => email notification
There are vbscript/powershell examples on how to connect to SQL server. You then would run the query the same way ....

You have to define what is the logic behind your question.
What is it you are looking the scripted process to do?

I.e. You run it every 15 minutes (half the time your current replication job runs)

"You can use SQL agent job to run the two SPs. Since you want monitoring, the SQL agent job needs to notify either after the result of running the sp for the publisher, the subscriber, etc. is hitting a specific threshold.
"
of course I don't mean that, I focus on how to use sp_showpendingchanges and everything in https://msdn.microsoft.com/en-us/library/ms147874(v=sql.100).aspx to make sure that everything is ok but those SP hard to use.

"What is it you are looking the scripted process to do?"

I can't successfully run the SP, I can't see why ? any example on how to use it?

Please post the exec sp @ and where you are running it, publisher/distributor or subscriber.

you mean the full command I execute, right?

Yes and where you are executing.
I.e. Distributor/publisher command
Subscriber command

basically I want to monitor the merge replication at all level in script level and  I am not sure if how well the merge replication is moving/doing !

so I might need:
1)  EXEC sp_showpendingchanges  ? really need to insert to a table and record all information, seems can't run this SP individually :

http://stackoverflow.com/questions/22164695/how-to-get-results-from-exec-sp-showpendingchanges-into-a-table

by that  page, https://msdn.microsoft.com/en-us/library/ms147874(v=sql.100).aspx, seems I don't need that as it said :

"Replication Monitor is a graphical tool that allows you to monitor a replication topology. You can access the SAME monitoring data programmatically using replication stored procedures"

so reading https://msdn.microsoft.com/en-us/library/ms147874(v=sql.100).aspx seems useless, right? just replication monitor is ok ? but replication monitor how show how many record is pending but only errors or no errors ,right?

I want to see how efficient is the merge replication:
1) latency in ms.
2) how many records are waiting to EACH subscriber.
3) what records are pending.
4) what it is waiting for ?

so I am expecting when I keep pressing F5 for that script/command, the result set will change, this is what I want.

if I do this :

use <database name>
;
go


EXEC sp_showpendingchanges 

Select allOpen in new window

then SP runs slow and :
1) I run that one publisher/distributor and it returns destination_Server as the subscriber ? pub_name is the publication name ? destination_dB_name is the DB the replication the publisher will replicate to ?
2) what is is_dest_subscriber is about? it is a number.
3)Pending_Delete is the number of records pending for delete?
4) pending_ins_and_upd is the number of records pending for ins/update? this column seems means both but how can it be?

anyway to show ONLY the item has pending records only ?

if I do this:

exec sp_showpendingchanges @destination_server= 'yyy', 
     @publication = '<publication>'   
    ,  @article= '<article>'
    ,  @show_rows=  0

Select allOpen in new window

what is show_rows = 0 is about ? I type 1 is shows nothing.

the link just said: If a value of 1 is specified, the result set contains the columns is_delete and rowguid.

but is_delete and rowguid is not in the result set of  sp_showpendingchanges.

also how to use this sp_replmonitorhelpmergesession and sp_replmonitorhelpmergesessiondetail   to monitor session information ? give me a sample command please.
https://msdn.microsoft.com/en-us/library/ms187726(v=sql.100).aspx
https://msdn.microsoft.com/en-us/library/ms186970(v=sql.100).aspx

it seem this 2 x command is very hard to find example from the web.

Try a simple straight query to see what you get with optional parameters.
From the link
sp_replmonitorhelpmergesession [ [ @agent_name= ] 'agent_name' ]
    [ , [ @hours= ] hours ]
    [ , [ @session_type= ] session_type ]
    [ , [ @publisher= ] 'publisher' ]
    [ , [ @publisher_db= ] 'publisher_db' ]
    [ , [ @publication= ] 'publication' ]

Try running with the agent name, publisher, and see how each variant ion changes the data.
As the note on the page for the above said, if you run it on the subscriber, you'll gets limited recent set of historical data.

Running it on the publisher/distributor .........

Best thing is by trial and error, try with the broadest @publisher='publisher'
And see what you get .....

Based on that you can see whether what you want is a processing that converts your responses into counts.

This will also help you define a context and basis for notification ...........

hi,

I think I experience one more issue here, can't see why and it only happen recently, we keep having replication alert like this :

REPLICATION ALERT LongRunningAgent for publication yyy on subscriber xxxx. Subscriber running for 46 minutes.

but when I check application log and replication monitor, nothing shows as error and the alert above only appear 10 hour ago only !

why can't find alert log, any replication error log path can see it from publisher?  please note that xxxx is not the publisher, is one of the subscriber, must I also check the error log from that subscriber too ?

Do you get these frequently? this seems to be an informational that is configured to notify should the Subscriber run in excess of a specified parameter at which this alert is generated with the duration.

Check the message headers to see when the email was generated and why it might have taken you this long to reach your inbox.


See if the below link helps you search for different alert types and perhaps see others within the same ...
https://msdn.microsoft.com/en-us/library/ms151752%28v=sql.105%29.aspx

I think the individual event checks/rechecks is way outside the scope of the initial question.

Sorry for the problem I saw above, can that SP help me to find out why ?

"Do you get these frequently? this seems to be an informational that is configured to notify should the Subscriber run in excess of a specified parameter at which this alert is generated with the duration.
"
yes, always.

but only these few days the log in the publisher don't show related information.

"Check the message headers to see when the email was generated and why it might have taken you this long to reach your inbox.
"

nono, I mean I received this email 10 hours ago during I am sleeping and this morning I come up and I find no log for this entries! I check all job log, application log and windows log. nothing related

I check this:
https://technet.microsoft.com/en-us/library/ms152758(v=sql.100).aspx

which one helps to check out replication latency most ?

"The message headers will tell you the origin of the mailing i.e. was it sent from the subscriber or the publisher."

yeah I know, you mean read the message header and where it comes from ,then read the log on that server, but not only on publisher to read all log for all publisher, distributor and subscriber, right?

someone tell me read all error  log /alert from publisher, right? but I also checked the log on subscribe (that one from subscriber), nothing related to this, is just last morning's alert, why disappear so quick ?

"The message headers will tell you the origin of the mailing i.e. was it sent from the subscriber or the publisher."

" believe there are several databases that are being replicated. The publisher might not have the bandwidth to replicate all of the data in real time, and that ..........
"

however ,we found out network connectivity issue between publisher and the DR server of that publisher, our network guy said it is the network hardware issue, so I will bring that up again.

"Identify the databases that are being replicated, see what the data size of the snapshots that are being sent and their frequency. Have a total of all of them summed up while doing that, get the bandwidth available to the subscribers."

so you mean here is the error/alert because of the network latency ?

hi, I am also looking for an update here, I hope you can coach me there too, this is another post :

https://www.experts-exchange.com/questions/28665420/how-to-use-SQL-profiler-to-check-SQL-query-overloading.html

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Not enough information to confirm an answer.

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

angelIII
Experts-Exchange Cleanup Volunteer

tks for that.  I might use Idera DM to monitor it first.

I closed ticket already.