Blog

Are you managing a lot of SQL Server instances? I was setting up a standardized set of SQL Server Alerts on a number of servers and soon realized this was something that could be automated by using a script.

So, here it is!

You’ll need to change the variables @DELAY_BETWEEN_RESPONSES and @OPERATOR_NAME. @DELAY_BETWEEN_RESPONSES limits how often an alert can be raised (don’t want to get spammed). And the operator should be set to a group with an email like dbateam@yourcompany.com (the email should obviously not be sent to only one person). An operator can be created in SSMS by right-clicking “Operators” under SQL Server Agent and selecting “New Operator”. Just enter a name and an email address.

You’ll need to review the list of alerts and errors as well. I normally set up alerts with severity 17-25. You may want to include severity 16 as well. I normally only use the alerts with severity, not individual errors. As you can see in the script below, I have included the error 9002 (log file full), but that is just to demonstrate how it is done.

Of course, you can edit the individual alerts after running the script. However, remember that it is possible to run the script several times (it drops and recreates them), and if you do, any changes will be overwritten.

Note that the script only sets up email alerts.

As usual, test before you run the script so that it is working as expected in your environment. I tried running the script as a multi server query, and it worked fine. However, remember that you’ll have as many individual transactions as you have connections to instances. So there is no way to do a complete rollback once you run it. You could handle the transactions manually perhaps. Or just run it against one instance at the time.

BEGIN TRANSACTION

/*************************************************
Alter these variables to your preferences:
*************************************************/
DECLARE
	@DELAY_BETWEEN_RESPONSES INT = 1800
	,@OPERATOR_NAME NVARCHAR(1000) = 'The DBA guys'

DECLARE
	@NO_TOTAL_ALERTS INT
	,@ROWPOSITION INT = 1
	,@ALERT_NAME VARCHAR(1000)
	,@SEVERITYNO INT
	,@ERRORNO INT

DECLARE @TBL_ALERTS TABLE
	(
		ROWCOUNTER INT IDENTITY(1,1) NOT NULL
		,SEVERITYNO INT NULL
		,ERRORNO INT NULL
		,ALERT_NAME VARCHAR(100) NOT NULL
	)

/*************************************************
Enter general alerts here:
*************************************************/
INSERT @TBL_ALERTS (SEVERITYNO, ALERT_NAME) VALUES
	(17, '017 - Insufficient Resources')
	,(18, '018 - Nonfatal Internal Error')
	,(19, '019 - Fatal Error in Resource')
	,(20, '020 - Fatal Error in Current Process')
	,(21, '021 - Fatal Error in Database Processes')
	,(22, '022 - Fatal Error: Table Integrity Suspect')
	,(23, '023 - Fatal Error: Database Integrity Suspect')
	,(24, '024 - Fatal Error: Hardware Error')
	,(25, '025 - Fatal Error')

/*************************************************
Enter alters on specific errors here:
(9002 is included for demonstration purposes only)
*************************************************/
INSERT @TBL_ALERTS (ERRORNO, ALERT_NAME) VALUES
	(9002, '9002 - Transaction log full')

	/* ADD MORE INDIVIDUAL ERRORS HERE */

SELECT @NO_TOTAL_ALERTS = COUNT(*) FROM @TBL_ALERTS

BEGIN TRY

	WHILE @ROWPOSITION <= @NO_TOTAL_ALERTS BEGIN

		SELECT
			@ALERT_NAME = ALERT_NAME + ' - ' + @@SERVERNAME
			,@SEVERITYNO = SEVERITYNO
			,@ERRORNO = ERRORNO
		FROM
			@TBL_ALERTS
		WHERE
			ROWCOUNTER = @ROWPOSITION

		--DROP IF ALREADY EXISTSING
		IF EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE [name] = @ALERT_NAME) BEGIN
			EXEC msdb.dbo.sp_delete_alert @name=@ALERT_NAME
		END

		IF @SEVERITYNO IS NOT NULL BEGIN
			EXEC msdb.dbo.sp_add_alert @name = @ALERT_NAME,
					@message_id=0, 
					@severity=@SEVERITYNO, 
					@enabled=1, 
					@delay_between_responses=@DELAY_BETWEEN_RESPONSES, 
					@include_event_description_in=1, 
					@job_id=N'00000000-0000-0000-0000-000000000000'
		END

		IF @ERRORNO IS NOT NULL BEGIN
			EXEC msdb.dbo.sp_add_alert @name = @ALERT_NAME,
					@message_id=@ERRORNO, 
					@severity=0, 
					@enabled=1, 
					@delay_between_responses=@DELAY_BETWEEN_RESPONSES, 
					@include_event_description_in=1, 
					@job_id=N'00000000-0000-0000-0000-000000000000'
		END

		EXEC msdb.dbo.sp_add_notification @alert_name=@ALERT_NAME, @operator_name=@OPERATOR_NAME, @notification_method = 1

		SELECT @ROWPOSITION = @ROWPOSITION + 1
	END
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()

	IF @@TRANCOUNT > 0 BEGIN
		ROLLBACK
	END
END CATCH

IF @@TRANCOUNT > 0 BEGIN
	COMMIT
END

[blue_box]
*** Update ***
Here is a very good list of individial errors you may want to use.

To generate insert statements to paste into the script above, use the following T-SQL:

SELECT
	',(' + CAST(message_id AS VARCHAR(10)) + ', ''' + CAST(message_id AS VARCHAR(10)) + ' - ' + SUBSTRING(REPLACE(text,'''',''), 1, 85) + CASE WHEN LEN(text) > 100 THEN ' ... ' ELSE '' END + ''')'
FROM
	sys.messages
WHERE
	language_id = 1033
AND
	message_id in
		(
			601,
			674,
			708,
			806,
			825,
			833,
			973,
			1205,
			3401,
			3410,
			3414,
			3422,
			3452,
			3619,
			3620,
			3959,
			5029,
			5144,
			5145,
			5182,
			8539,
			8540,
			9001,
			14157,
			14161,
			17173,
			17179,
			17883,
			17884,
			17887,
			17888,
			17890,
			17891,
			18401,
			20572,
			20574
		)

Note that it needs to be a logged message for the alert to fire. If the message will be logged or not can be seen in the column is_event_logged in sys.messages. To alter this for the messages above, use the following template:

EXEC sp_altermessage @message_id = 601, @parameter = 'WITH_LOG' ,@parameter_value = 'true'
GO

EXEC sp_altermessage @message_id = 833, @parameter = 'WITH_LOG' ,@parameter_value = 'true'
GO

EXEC sp_altermessage @message_id = 1205, @parameter = 'WITH_LOG' ,@parameter_value = 'true'
GO

[/blue_box]