2

We would like to stop getting sql server alerts from dev servers during the weekend. We have alerts for severity 016 to 025.

1
  • Is the alert running a sql agent job to send you email alert ?
    – Kin Shah
    Nov 20, 2018 at 17:33

1 Answer 1

2

Probably the easiest way I can think of is to set up an agent job to disable/enable the agent on a schedule.

https://learn.microsoft.com/en-us/sql/ssms/agent/disable-or-reactivate-an-alert?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/4848/getting-started-with-sql-server-agent--part-1/

-- changes the enabled setting of Test Alert to 0  
USE msdb ;  
GO  

EXEC dbo.sp_update_alert  
    @name = N'Test Alert',  
    @enabled = 0 ;  
GO  

You can generate the required code for all alerts that fire a notification by running this:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

This will generate the required code to re-enable those alerts:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

I'd add the generated "disable" code to an agent job that runs on Friday evening, and the "enable" code into a job that runs on Monday morning.

3
  • One more question, I disabled the alerts for our dev servers and If I get a severity 020 error sometime in the weekend, would i still be able to see the error on the Sql Server error log on Monday?
    – dani
    Nov 20, 2018 at 18:46
  • Disabling the alert will also disable any logging for the alert. Instead of disabling it you could use a similar method do disable the email alert setting. Nov 21, 2018 at 0:37
  • Now that I think about it, it would be easier to just have two sets of alerts that you enable/disable on the schedule using the agent jobs. One set would have the email settings established and the other set, to run on the weekends, wouldn't. Much easier than trying to edit the alert settings. Nov 21, 2018 at 1:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.