We would like to stop getting sql server alerts from dev servers during the weekend. We have alerts for severity 016 to 025.
1 Answer
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.
-
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?– daniNov 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