2

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

  • Is the alert running a sql agent job to send you email alert ? – Kin Shah Nov 20 '18 at 17:33
  • Dani, if the answer given was helpful, please consider marking it as the accepted answer. Thanks. – Shooter McGavin Nov 30 '18 at 16:08
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://docs.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.

  • 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 '18 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. – Shooter McGavin Nov 21 '18 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. – Shooter McGavin Nov 21 '18 at 1:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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