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
GOPublished @ 2019-08-16 8:02 pm