stakes (12/9/2010)

---

Got the solution!

To get the Alert working, you should leave the Type to 'SQL Server event alert', database name to <all databases> and the error number to 14161. The problem is that the Alert won't fire, because the shouldalert parameter isn't set to 1 (which to my opinion automatically should be done when configuring Replication or the Alert). To see if the shouldalert parameter is set to 0 or 1, execute:

SELECT TOP 2 [publication_id]

,[metric_id]

,[value]

,[shouldalert]

,[isenabled]

FROM [distribution].[dbo].[MSpublicationthresholds]

To change it:

exec [distribution].sys.sp_replmonitorchangepublicationthreshold

@publisher = N'STSQLSERVER3',

@publisher_db = N'AdventureWorks',

@publication = N'AWPersonPublication',

@metric_id = 2,--metric_id 1=latency, 2=subscription expire

@value = 30,--Enter Threshold here

@shouldalert = 1,

@mode = 1

As you can see, you can also change the Threshold value with this command. Another tip: the Warnings tab in the Replication Monitor doesn't refresh automatically. So do that if you want to see the result after executing the above command.