SQL Alerts


Esse é um conjunto de Alertas que todo o DBA deveria ter em seu SQL.

Para evitar gerar uma montanha de email sem necessidade ele tem um intervalo entre notificação de +/- 15min.

USE [msdb];
GO

SET NOCOUNT ON;


-- Troque o @OperatorName 
DECLARE @OperatorName sysname = N'DBA';

IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = @OperatorName)
BEGIN
EXEC msdb.dbo.sp_add_operator @name=@OperatorName, 
		@enabled=1, 
		@weekday_pager_start_time=90000, 
		@weekday_pager_end_time=180000, 
		@saturday_pager_start_time=90000, 
		@saturday_pager_end_time=180000, 
		@sunday_pager_start_time=90000, 
		@sunday_pager_end_time=180000, 
		@pager_days=0, 
		@email_address=N'dba@mc1.com.br', 
		@category_name=N'[Uncategorized]'
END


-- Troque @CategoryName se precisar
DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';


-- Adiciona a categoria se ela nao existir
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

-- Nome do servidor
DECLARE @ServerName sysname = (SELECT @@SERVERNAME);


-- Alertas comecam com o nome do servidor
DECLARE @Sev16AlertName sysname = @ServerName + N' Alert - Sev 16 Error: Miscellaneous User Error';
DECLARE @Sev17AlertName sysname = @ServerName + N' Alert - Sev 17 Error: Insufficient Resources';
DECLARE @Sev18AlertName sysname = @ServerName + N' Alert - Sev 18 Error: NonFatal Internal Error';
DECLARE @Sev19AlertName sysname = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName sysname = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName sysname = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName sysname = @ServerName + N' Alert - Sev 22 Error: Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName sysname = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName sysname = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName sysname = @ServerName + N' Alert - Sev 25 Error: Fatal Error';
DECLARE @Error823AlertName sysname = @ServerName + N' Alert - Error 823: The operating system returned an error';
DECLARE @Error824AlertName sysname = @ServerName + N' Alert - Error 824: Logical consistency-based I/O error';
DECLARE @Error825AlertName sysname = @ServerName + N' Alert - Error 825: Read-Retry Required';
DECLARE @Error832AlertName sysname = @ServerName + N' Alert - Error 832: Constant page has changed';
DECLARE @Error855AlertName sysname = @ServerName + N' Alert - Error 855: Uncorrectable hardware memory corruption detected';
DECLARE @Error856AlertName sysname = @ServerName + N' Alert - Error 856: SQL Server has detected hardware memory corruption, but has recovered the page';
DECLARE @Error833AlertName sysname = @ServerName + N' Alert - Error 833: I/O requests taking longer to respond';
DECLARE @Error1443AlertName sysname = @ServerName + N' Alert - Error 1443: Database mirroring has been terminated for database';



-- Sev 16 Error: Miscellaneous User Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev16AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev16AlertName, 
				  @message_id = 0, @severity = 16, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev16AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev16AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END

-- Sev 17 Error: Insufficient Resources
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev17AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev17AlertName, 
				  @message_id = 0, @severity = 17, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev17AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev17AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END

-- Sev 18 Error: NonFatal Internal Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev18AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev18AlertName, 
				  @message_id = 0, @severity = 18, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev18AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev18AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END
	
-- Sev 19 Error: Fatal Error in Resource
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev19AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName, 
				  @message_id = 0, @severity = 19, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev19AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Sev 20 Error: Fatal Error in Current Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev20AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName, 
				  @message_id = 0, @severity = 20, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000'

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev20AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Sev 21 Error: Fatal Error in Database Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev21AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName, 
				  @message_id = 0, @severity = 21, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev21AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Sev 22 Error: Fatal Error Table Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev22AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName, 
				  @message_id = 0, @severity = 22, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev22AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Sev 23 Error: Fatal Error Database Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev23AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName, 
				  @message_id = 0, @severity = 23, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev23AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Sev 24 Error: Fatal Hardware Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev24AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName, 
				  @message_id = 0, @severity = 24, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1,
				  @category_name = @CategoryName, 
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev24AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Sev 25 Error: Fatal Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev25AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName, 
				  @message_id = 0, @severity = 25, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1, 
				  @category_name = @CategoryName,
				  @job_id = N'00000000-0000-0000-0000-000000000000';

-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev25AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END

-- Error 823 

-- Error 823: Operating System Error
-- How to troubleshoot a Msg 823 error in SQL Server	
-- http://support.microsoft.com/kb/2015755
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error823AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName, 
				  @message_id = 823, @severity = 0, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1, 
				  @category_name = @CategoryName, 
				  @job_id  = N'00000000-0000-0000-0000-000000000000';


-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error823AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END
	
-- Error 824 Alert

-- Error 824: Logical consistency-based I/O error
-- How to troubleshoot Msg 824 in SQL Server
-- http://support.microsoft.com/kb/2015756
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error824AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName, 
				  @message_id = 824, @severity = 0, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1, 
				  @category_name = @CategoryName, 
				  @job_id  = N'00000000-0000-0000-0000-000000000000';


-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error824AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Error 825: Read-Retry Required
-- How to troubleshoot Msg 825 (read retry) in SQL Server
-- http://support.microsoft.com/kb/2015757
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error825AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName, 
				  @message_id = 825, @severity = 0, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1, 
				  @category_name = @CategoryName, 
				  @job_id  =N'00000000-0000-0000-0000-000000000000';


-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error825AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END

-- Error 832 Alert

-- Error 832: Constant page has changed
-- http://www.sqlskills.com/blogs/paul/dont-confuse-error-823-and-error-832/
-- http://support.microsoft.com/kb/2015759
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error832AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Error832AlertName, 
				  @message_id = 832, @severity = 0, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1, 
				  @category_name = @CategoryName, 
				  @job_id  = N'00000000-0000-0000-0000-000000000000';


-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error832AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error832AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Error 833

-- Error 833: I/O Taking longer to respond
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error833AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Error833AlertName, 
				  @message_id = 833, @severity = 0, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1, 
				  @category_name = @CategoryName, 
				  @job_id  = N'00000000-0000-0000-0000-000000000000';


-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error833AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error833AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END


-- Error 1443

-- Error 1443: Database mirroring has been terminated for database
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error1443AlertName)
	EXEC msdb.dbo.sp_add_alert @name = @Error1443AlertName, 
				  @message_id = 1443, @severity = 0, @enabled = 1, 
				  @delay_between_responses = 900, @include_event_description_in = 1, 
				  @category_name = @CategoryName, 
				  @job_id  = N'00000000-0000-0000-0000-000000000000';


-- Adiciona a notificacao se ela nao existir
IF NOT EXISTS(SELECT *
		      FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error1443AlertName)
	BEGIN
		EXEC msdb.dbo.sp_add_notification @alert_name = @Error1443AlertName, @operator_name = @OperatorName, @notification_method = 1;
	END

-- Memory Error Correction


-- SQL Server 2012 ou superior e Enterprise Edition
IF LEFT(CONVERT(CHAR(2),SERVERPROPERTY('ProductVersion')), 2) >= '11' AND SERVERPROPERTY('EngineEdition') = 3
    BEGIN
        -- Error 855: Uncorrectable hardware memory corruption detected
		IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error855AlertName)
			EXEC msdb.dbo.sp_add_alert @name = @Error855AlertName, 
						  @message_id = 855, @severity = 0, @enabled = 1, 
						  @delay_between_responses = 900, @include_event_description_in = 1, 
						  @category_name = @CategoryName, 
						  @job_id  = N'00000000-0000-0000-0000-000000000000';


		-- Adiciona a notificacao se ela nao existir
		IF NOT EXISTS(SELECT *
					  FROM dbo.sysalerts AS sa
					  INNER JOIN dbo.sysnotifications AS sn
					  ON sa.id = sn.alert_id
					  WHERE sa.name = @Error855AlertName)
			BEGIN
				EXEC msdb.dbo.sp_add_notification @alert_name = @Error855AlertName, @operator_name = @OperatorName, @notification_method = 1;
			END

		-- Error 856: SQL Server has detected hardware memory corruption, but has recovered the page
		IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error856AlertName)
			EXEC msdb.dbo.sp_add_alert @name = @Error856AlertName, 
						  @message_id = 856, @severity = 0, @enabled = 1, 
						  @delay_between_responses = 900, @include_event_description_in = 1, 
						  @category_name = @CategoryName, 
						  @job_id  = N'00000000-0000-0000-0000-000000000000';


		-- Adiciona a notificacao se ela nao existir
		IF NOT EXISTS(SELECT *
					  FROM dbo.sysalerts AS sa
					  INNER JOIN dbo.sysnotifications AS sn
					  ON sa.id = sn.alert_id
					  WHERE sa.name = @Error856AlertName)
			BEGIN
				EXEC msdb.dbo.sp_add_notification @alert_name = @Error856AlertName, @operator_name = @OperatorName, @notification_method = 1;
			END
    END
GO

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s