Configure Alerts SQL Server

As a base set of alerts for monitoring SQL Server I tend to use the following out of the box categories, with the obvious exception of syntax errors. This will cover all of the really bad alerts that you never want to see, as well as those that can help troubleshoot and head off potential issues.

The only additional work needed is to confirm that you have appropriate alerting setup which in most cases will be database mail.

I have included the syntax to reset the counters if you would like to periodically zero them.

Finally included is a script which shows how to add logging for messages that are not by default raised.

--http://jongurgul.com/blog/configure-alerts-sql-server
DECLARE @OperatorName NVARCHAR(1000),@OperatorNameEmail NVARCHAR(1000),@AlertName NVARCHAR(4000),@AlertSeverity INT
SET @OperatorName = N'SQLALERTS'

IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'SQLALERTS')
BEGIN
EXEC msdb.dbo.sp_add_operator @name=@OperatorName,@enabled=1,@email_address=N'sqlalerts@yourdomain.com'
END

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR SELECT DISTINCT [severity] FROM sys.messages WHERE [severity] IN (14,16,17,18,19,20,21,22,23,24) UNION ALL SELECT 25 ORDER BY [severity]		
OPEN c;
FETCH NEXT FROM c INTO @AlertSeverity;			
WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		SET @AlertName = N'Severity ' + RIGHT('000'+CAST(@AlertSeverity AS VARCHAR(3)),3)
		IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @AlertName)
		BEGIN
			PRINT 'Deleting Alert: '+@AlertName
			EXEC msdb.dbo.sp_delete_alert @name=@AlertName
		END
			PRINT 'Creating Alert: '+@AlertName
			EXEC msdb.dbo.sp_add_alert @name=@AlertName, 
					@message_id=0, 
					@severity=@AlertSeverity, 
					@enabled=1, 
					@delay_between_responses=0, 
					@include_event_description_in=1, 
					@category_name=N'[Uncategorized]', 
					@job_id=N'00000000-0000-0000-0000-000000000000'
			PRINT 'Adding Alert: '+@AlertName + ' Notification ' + @OperatorName
			EXEC msdb.dbo.sp_add_notification @alert_name=@AlertName,@operator_name=@OperatorName,@notification_method = 1
	END TRY
	BEGIN CATCH
		PRINT ERROR_MESSAGE();
	END CATCH
	FETCH NEXT FROM c INTO @AlertSeverity;
END			
CLOSE c;
DEALLOCATE c;

/*
--Reset counters
DECLARE @curr_date INT,@curr_time INT,@dt DATETIME
SET @dt = CURRENT_TIMESTAMP 
SELECT @curr_date = CAST(CONVERT(CHAR, @dt,112) AS INT),@curr_time=(DATEPART(hh,@dt)*10000)+(DATEPART(mi,@dt)*100)+DATEPART(ss,@dt)
   
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 014',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 016',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 017',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 018',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 019',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 020',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 021',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 022',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 023',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 024',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 025',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
*/
/*
Now not all alerts are raise to the log, therefore in some cases you may wish to change this behaviour. For example permissions issue could be raise if we tell sql to log them.

SELECT * FROM sys.messages WHERE [message_id] = 229
The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.
sp_altermessage (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms175094.aspx
EXEC msdb.dbo.sp_altermessage 229,'WITH_LOG','true';
*/

http://gallery.technet.microsoft.com/scriptcenter/Configure-Alerts-SQL-Server-b4b2a14d

One thought on “Configure Alerts SQL Server

  1. Pingback: Suppress messages in SQL log | Jon Gurgul's Blog

Leave a Reply