SQL Server Agent Alerts : Part 2

We can use the below script to create SQL Server Agent alerts to notify the operator in case of any Data Consistency Errors in Replication

Below are the common data consistency errors that can occur:

• 2601 Cannot insert a duplicate key row in object
• 20598 The row was not found at the Subscriber when applying the replicated command.
• 2627 Violation of PRIMARY KEY constraint 'PK_'

USE [msdb];
GO

SET NOCOUNT ON;

-- Change @OperatorName as needed
DECLARE @OperatorName SYSNAME = N'DBAOperator';
-- Change @CategoryName as needed
DECLARE @CategoryName SYSNAME = N'SQL Server Agent Alerts';

-- Make sure you have an Agent Operator defined that matches the name you supplied
IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.sysoperators
		WHERE name = @OperatorName
		)
BEGIN
	RAISERROR (
			'There is no SQL Operator with a name of %s'
			,18
			,16
			,@OperatorName
			);

	RETURN;
END

-- Add Alert Category if it does not exist
IF NOT EXISTS (
		SELECT *
		FROM msdb.dbo.syscategories
		WHERE category_class = 2 -- ALERT
			AND category_type = 3
			AND name = @CategoryName
		)
BEGIN
	EXEC dbo.sp_add_category @class = N'ALERT'
		,@type = N'NONE'
		,@name = @CategoryName;
END

-- Get the server name
DECLARE @ServerName SYSNAME = (
		SELECT @@SERVERNAME
		);
--AG
DECLARE @Error2601AlertName SYSNAME = @ServerName + N' Alert - Replication: Cannot insert duplicate key row in object';
DECLARE @Error20598AlertName SYSNAME = @ServerName + N' Alert - Replication: The row was not found at the Subscriber';
DECLARE @Error2627AlertName SYSNAME = @ServerName + N' Alert - Replication: Violation of PRIMARY KEY constraint';

IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error2601AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error2601AlertName
		,@message_id = 2601
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 600
		,@include_event_description_in = 5
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error2601AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error2601AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error20598AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error20598AlertName
		,@message_id = 20598
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 600
		,@include_event_description_in = 5
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error20598AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error20598AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END

IF NOT EXISTS (
		SELECT name
		FROM msdb.dbo.sysalerts
		WHERE name = @Error2627AlertName
		)
	EXEC msdb.dbo.sp_add_alert @name = @Error2627AlertName
		,@message_id = 2627
		,@severity = 0
		,@enabled = 1
		,@delay_between_responses = 0
		,@include_event_description_in = 5
		,@category_name = @CategoryName
		,@job_id = N'00000000-0000-0000-0000-000000000000'

IF NOT EXISTS (
		SELECT *
		FROM dbo.sysalerts AS sa
		INNER JOIN dbo.sysnotifications AS sn ON sa.id = sn.alert_id
		WHERE sa.name = @Error2627AlertName
		)
BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name = @Error2627AlertName
		,@operator_name = @OperatorName
		,@notification_method = 1;
END
GO

Hope it helps !!