none
How to suppress Log Shipping alert for a specific database? RRS feed

  • Question

  • I want to disable log shipping for a database temporarily.   I have disabled the backup job, copy job, and restore job created automatically when log shipping is configured for the database.  However, I cannot disable the Log Shipping alert job since there are other databases configured for log shipping.   How can I suppress Log Shipping alert for specific database?  I don't want to disable the log shipping for the primary database since it will delete all jobs and history related to the log shipping configuration at the primary, secondary and monitor server instances.

     

    Tuesday, May 24, 2011 5:48 PM

Answers

  • Hi PCSQL66,

    This is not possible unless you break and reconfigure log shipping for the specific database in question. This is simply because the alert job runs a stored proc that hits the metadata table that basically stores information about all the databases configured for LS in the instance and looks at particular threshold for each db to trigger the alerts.

    If you know what your outage is going to be for this specific database , you can increase the out of sync or alert if no backup,copy or restore occurs option to a higher value until you know this is fixed. Then you could change those above mentioned parameters so that the LS agent alert job doesn't send msgs for this database for now


    Thanks, Leks

    Saturday, June 4, 2011 11:37 PM
  • Hi,

    Thanks for your update. If this is the case, you may need to disable log shipping alert job both on primary server and secondary server. Please note that you won't get alert for any other log shipping databases since only one alert job is created for the entire server instance.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, June 1, 2011 6:09 AM
    Moderator

All replies

  • Hi,

    You may temporarily remove Monitor Server for this log shipping database, and you can add it when you want to enable log shipping. 

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, May 30, 2011 6:46 AM
    Moderator
  • http://msdn.microsoft.com/en-us/library/aa215391(v=sql.80).aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, May 30, 2011 7:20 AM
  • Monitor Server is not used.  So, alert jobs are created locally on the primary server instance and each secondary server instance.

    This log shipping is in SQL Server 2005 so sp_delete_log_shipping_monitor_info will not work since it is for SQL Server 2000.  

     

     

    Tuesday, May 31, 2011 7:12 PM
  • Hi,

    Thanks for your update. If this is the case, you may need to disable log shipping alert job both on primary server and secondary server. Please note that you won't get alert for any other log shipping databases since only one alert job is created for the entire server instance.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, June 1, 2011 6:09 AM
    Moderator
  • Hi PCSQL66,

    This is not possible unless you break and reconfigure log shipping for the specific database in question. This is simply because the alert job runs a stored proc that hits the metadata table that basically stores information about all the databases configured for LS in the instance and looks at particular threshold for each db to trigger the alerts.

    If you know what your outage is going to be for this specific database , you can increase the out of sync or alert if no backup,copy or restore occurs option to a higher value until you know this is fixed. Then you could change those above mentioned parameters so that the LS agent alert job doesn't send msgs for this database for now


    Thanks, Leks

    Saturday, June 4, 2011 11:37 PM
  • Too late but this is possible. We just need to set the value threshold_alert_enabled to 0 in the system table msdb.dbo.log_shipping_monitor_primary on the primary server and in msdb.dbo.log_shipping_monitor_secondary on the secondary server. I just tested it out and it should work.

    To test this out, perform the below:-

    1. Run exec master.sys.sp_check_log_shipping_monitor_alert will show the same error message that thresholds have been crossed. This is the same script used in LSAlert job.

    Use something like the below command to edit:-

    2. Run the below to change the values:

    update msdb.dbo.log_shipping_monitor_primary
    set threshold_alert_enabled = 0
    where primary_database = 'XYZ'

    Run this for all databases that needs to be excluded from monitoring.

    3. Run the script in step 1 again and it should run now. 


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    • Proposed as answer by ecks51 Tuesday, March 27, 2018 5:50 PM
    Tuesday, February 24, 2015 12:57 AM
  • I know this is old.. but it's  Still valid:

    See Kartar's Answer - This is exactly how to do it! 

    1. msdb.dbo.log_shipping_monitor_primary
    2. msdb.dbo.log_shipping_monitor_secondary
    3. Set threshold_alert_enabled to 0

    In my case, I had to set (threshold_alert_enabled) to False instead of 0 (SQL 2008R2)

    • Edited by ecks51 Tuesday, March 27, 2018 5:52 PM
    Tuesday, March 27, 2018 5:50 PM