There are two user Input parameters required, which are stated as follows: DistributionDb : Database used for the distributing replication Transactions. Log Reader Agent Name : Name for the log reader Agent.
Note : Following is the stored procedure, which needs to be Executed against the distribution database,
If no results are returned, then alert as configured monitoring incorrectly.
Statement
exec $DistributionDB$.dbo.sp_MSenum_logreader @name = N'$LogReaderAgentName$', @show_distdb = 0
Example :
exec [distribution].dbo.sp_MSenum_logreader @name = N'HMDXASP38-LTCabc-1', @show_distdb = 0
Example Audit Report :
Name : HMDXASP31
status : 3
Publisher : HMDXASP38
Publisher_db : LTCabc
start_time : 20051113 09:20:29.800
time : 20051115 18:22:35.973
duration : 205326
comments : 18 transaction(s) with 35 command(s) were delivered.
delivery_time : 2506390
delivered_transactions : 823126
delivered_commands : 2862661
average_commands : 3
delivery_rate : 1142
delivery_latency : 0
error_id : 0
job_id : 0x459FD4569335CD4EB7B8FA62F92FBFB8
local_job : 1
profile_id : 2
agent_id : 1
last_timestamp : 0x00000000000B3D84
The above Information is what returned by executing the command. The stats highlighted in red are the stats, which
Require threshold and alerts should be fired if they do not fall in between the Min(Threshould) and Max(Threshould).
Threshold for the “Status”:
When Status :1 = Start
When Status :2 = Succeed
When Status :3 = In progress When Status :4 = Idle
When Status :5 = Retry
When Status :6 = Fail
The thresholds would be passed in for the above alert in multiple values, separated by comma “,” .
Example : Raise alert when Status != (1,2,3)
Threshold for the “Delivery Latency”
This should alert when latency crosses the max(Threshold).
Example : When Delivery_Latency > 50000 Then Alert.
