Enable Or Disable All Alerts In SQL Server

The alerts are configured in SQL Server Agent to alert us when there is a hit on a threshold. Alert by its name notifies us based on the severity that occurs for any specific error happening on SQL Server Agent. The uses of these thresholds are simply incredible, and they are used widely in many companies across the globe.

As you know, a DBA would be an expert already in configuring an alert to keep up to date on any events or errors happening on the server. Having said that, there are many instances where multiple alerts would be required to enable or disable at once.

Let’s check a simple code to save our time to enable or disable all alerts at once.

General syntax to disable an alert:

exec msdb..sp_update_alert @name = 'your alert name here', @enabled = 0

General syntax to enable an alert:

exec msdb..sp_update_alert @name = 'your alert name here', @enabled = 1

Enable or Disable all jobs at once

For Disable:

SELECT 'exec msdb..sp_update_alert @name = '''+NAME+''', @enabled = 0' FROM msdb..sysalerts

Enable or Disable Alerts In SQL Server 1

For Enable:

SELECT 'exec msdb..sp_update_alert @name = '''+NAME+''', @enabled = 1' FROM msdb..sysalerts

Enable or Disable Alerts In SQL Server

Please use the above code to get the results of all alerts from msdb..sysalerts table. You can now use them to either enable or disable the alert as per your convenience in a new query window.

You may also find this helpful: 

Enable Or Disable All Jobs In SQL Server

0 comments: