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
For Enable:
SELECT 'exec
msdb..sp_update_alert @name = '''+NAME+''', @enabled =
1' FROM msdb..sysalerts
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:
0 comments:
Post a Comment