SQL Server – How to Turn Off Alerts

alertssql serversql-server-2008-r2sql-server-agent

We have a SQL Server 2008 R2 database instance running on a VM. Nightly, between 10p and 12a, we are getting the following error message:

DATE/TIME: 2/3/2014 10:16:37 PM

DESCRIPTION: Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://xxxxx.xxxx.xxxx.xxx:5023'.

We have determined these alerts are happening at the same time the Veeam backup up is running.

Is there a way to turn OFF the SQL Server Alerts from 10p to 12a ?

Best Answer

To answer your direct question, I'd probably go about that with a dedicated SQL Server Agent Job to toggling the alert enabled status with the duration of that time.

To disable that alert:

use msdb;
go

exec dbo.sp_update_alert @name = 'YourAlertName', @enabled = 0;
go

And then 4 hours later (either as a separately scheduled job, or with just a WAITFOR DELAY in the initial job that does the disabling):

use msdb;
go

exec dbo.sp_update_alert @name = 'YourAlertName', @enabled = 1;
go

That's the short answer. But the more thorough one would be a question... Why is your process during this time causing this error? Unfortunately I can't advise without digging quite a bit deeper into what that is or what it is doing, as I'm not familiar with Veeam. But the point I'm trying to get across is, instead of silencing an alert during this timespan, I'd be even more interested in finding out why that process is causing that error, in turn triggering the alert.

Related Question