How to Monitor Replication Log Reader Agent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to Monitor Replication Log Reader Agent

Author

Topic

shaileshpatangay
Starting Member

4 Posts

Posted - 2009-06-29 : 09:46:58

How to Monitor Replication Log Reader Agent

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.

cheers
shailesh patangay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources