Wednesday 5 May 2021

SQL Server Always On Monitoring Alerts

Set Nocount On;

Declare @alertName sysname
, @thisErrorNumber varchar(6)
, @sqlCommand nvarchar(max) = ''
, @operatorName sysname = 'Database Administration';

Declare @errorNumbers Table (ErrorNumber varchar(6), AlertName varchar(50));

Insert Into @errorNumbers
Values ('1480' , 'AG Role Change (failover)')
, ('976' , 'Database Not Accessible')
, ('983' , 'Database Role Resolving')
, ('3402' , 'Database Restoring')
, ('19406', 'AG Replica Changed States')
, ('35206', 'Connection Timeout')
, ('35250', 'Connection to Primary Inactive')
, ('35264', 'Data Movement Suspended')
, ('35273', 'Database Inaccessible')
, ('35274', 'Database Recovery Pending')
, ('35275', 'Database in Suspect State')
, ('35276', 'Database Out of Sync')
, ('41091', 'Replica Going Offline')
, ('41131', 'Failed to Bring AG Online')
, ('41142', 'Replica Cannot Become Primary')
, ('41406', 'AG Not Ready for Auto Failover')
, ('41414', 'Secondary Not Connected');

Declare cur_ForEachErrorNumber Cursor Local fast_forward
For
Select *
From @errorNumbers;

Open cur_ForEachErrorNumber;
Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;

While @@fetch_status = 0
Begin

If Not Exists(Select *
From msdb.dbo.sysalerts s
Where s.message_id = @thisErrorNumber)
Begin

Execute msdb.dbo.sp_add_alert
@name = @alertName
, @message_id = @thisErrorNumber
, @severity = 0
, @enabled = 1
, @delay_between_responses = 0
, @include_event_description_in = 1
, @job_id = N'00000000-0000-0000-0000-000000000000';

Execute msdb.dbo.sp_add_notification
@alert_name = @alertName
, @operator_name = @operatorName
, @notification_method = 1;

Raiserror('Alert ''%s'' for error number %s created.', -1, -1, @alertName, @thisErrorNumber) With nowait;
End

Fetch Next From cur_ForEachErrorNumber Into @thisErrorNumber, @alertName;
End

--==== Close/Deallocate cursor
Close cur_ForEachErrorNumber;
Deallocate cur_ForEachErrorNumber;

1 comment:

  1. Hi,

    Thank you for the script. However, I recommend changing the @delay_between_responses to at least 120 (which is equal to 2 minutes). Otherwise, you will receive thousands of emails for a simple restart on one of the servers.

    Thanks,

    Ata

    ReplyDelete