none
Replication Warning: Subscription expiration alert doesn't work RRS feed

  • Question

  • I am having the same trouble with getting the  "Replication Warning: Subscription expiration" alert to work.

    It does not alert before the subscription is marked expired and needs to be reintialized.

    Does anyone know how to configure that alert so that it works if the default: SQL Server event Alert\14160 is not what makes the alert fire.

    And possibly an easy way to test it.

    Thanks!

     

    As I try to trouble shoot I found a table MSpublicationthresholds that has a column:

    shouldalert: A value of 1 indicates that an alert should be generated when the metric exceeds the defined threshold.

    I cannot figure how to set that value other than directly update the table.

    Note replication monitor interface has an enable check box that affects this field:

    isenabled: A value of 1 indicates that monitoring is enabled for this replication performance metric.

    It is as though you can't turn shouldalert on via the GUI.

    Even if I do manually change it or with the proc below, there is still the question about having to reconfigure the default alert to get it to work?

    There is the proc that the replmon uses to change the isenabled value:


    exec [distribution].sys.sp_replmonitorchangepublicationthreshold @publisher = N'ASTOF-DBA03T', @publisher_db = N'PublTest', @publication = N'TestPub', @metric_id = 1, @value = 75, @shouldalert = 0, @mode = 1

    But shouldalert is 0

    If anyone has solved this before I would appreciate the help.  Thanks!

    Thursday, July 1, 2010 4:19 PM

Answers

  • Hi,

    Please check the replication alerts and warnings configuration using the following steps:
    1. Connect to an instance of SQL Server in SQL Server Management Studio, and then expand the server node.
    2. Right-click the Replication folder or any of its subfolders, and then click Launch Replication Monitor.
    3. Expand a Publisher group in the left pane, expand a Publisher, and then click a publication.
    4. Click the Warnings tab.
    5. Enable a warning by selecting the check box Warn if a subscription will expire within the threshold.
    6. Set a threshold for the warning in the Threshold column.
    7. Click Configure Alerts.
    8. In the Configure Replication Alerts dialog box, select an alert “Subscription expiration (Threshold: expiration)”, and then click Configure.
    9. Please check setting of the alert properties; specially Enable checkbox, database name, Error number textbox.
    10.  In the History page, you cloud check the date of last alert and number of occurrences.


    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, July 6, 2010 5:43 AM
    Moderator

All replies

  • You are on the right track...  Just use that proc that Replication Monitor uses to change that value.

    exec [distribution].sys.sp_replmonitorchangepublicationthreshold
    	@publisher = N'ASTOF-DBA03T',
    	@publisher_db = N'PublTest',
    	@publication = N'TestPub',
    	@metric_id = 1,
    	@value = 75,
    	@shouldalert = 1,
    	@mode = 1

    Just set the @shouldalert value to 1.  Unfortunately, I haven't found a way with the GUI to modify this either.  You'll need to do this on all of your alerts.

    Hope this helps.

    Jarret

    Thursday, July 1, 2010 7:41 PM
    Moderator
  • I have set shouldalert to 1 along with @mode to 1 and lowered the value to 1%.

    I also changed the

    "Subscriptions expire and may be dropped if not synchoronized in the following number of hours": Interval to 1 hour.  The lowest possible.

    I then have updated the publisher table and blocked the Subscriber with a holdlock.  The 2 tables have been out of sync for at least 3 hours and still no error message.

    I have gotten this message in the logs that I have not seen before but it happend after I set the shouldalert and does not seem to be related to the tables not in sync:

    The threshold [latency:The time taken to replicate data from the transactional publisher (committed) to the subscriber (committed) - in seconds] for the publication [TestPub] has been set. Make sure that the logreader and distribution agents are running and can match the latency requirement.

    That is error number 14161...oddly enough that error is not in the sysreplicationalerts table though it got put in the sql server error log.http://technet.microsoft.com/en-us/library/ms152467(SQL.90).aspx

    The alert keys off 14160 though and that has not come through.

    I need to find out the proc or code that does the check to see if the 14160 error threshold has been passed.  Because I have definitely passed 1% or 1 hour.  

    I am wondering if the default 72 hours distribution retention(select * from msdb.dbo.MSdistributiondbs) has to expire first before the Subscription alert goes off.

    Has anyone ever setup this alert successfully and got it to fire off?

    If so how can I test it.

     

    Thanks, Leon

     

     

     

     

     

     

     

    Thursday, July 1, 2010 8:50 PM
  • Hi,

    Please check the replication alerts and warnings configuration using the following steps:
    1. Connect to an instance of SQL Server in SQL Server Management Studio, and then expand the server node.
    2. Right-click the Replication folder or any of its subfolders, and then click Launch Replication Monitor.
    3. Expand a Publisher group in the left pane, expand a Publisher, and then click a publication.
    4. Click the Warnings tab.
    5. Enable a warning by selecting the check box Warn if a subscription will expire within the threshold.
    6. Set a threshold for the warning in the Threshold column.
    7. Click Configure Alerts.
    8. In the Configure Replication Alerts dialog box, select an alert “Subscription expiration (Threshold: expiration)”, and then click Configure.
    9. Please check setting of the alert properties; specially Enable checkbox, database name, Error number textbox.
    10.  In the History page, you cloud check the date of last alert and number of occurrences.


    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, July 6, 2010 5:43 AM
    Moderator
  • I tried to follow the steps outlined by Xiao-Min Tan  to configure a custom alert with the "warn if a subscription will expire soon" warning, but my custom alert for error # 14160 doesn't ahow up as an available alert in replication monitor when I click on Configure Alerts.  Has anyone managed to get this to work so an email is sent when the threshold is reached?
    Chuck Hottle
    Monday, April 4, 2011 7:03 PM