Create Standard SQL Agent Alerts For SQL Server
- Corey Schafer
- Oct 11, 2019
- 7 min read
USE [msdb]
GO
------------------------------------------
--- Alerts Mega-Script ---
--- Corey Schafer (c) 2019 ---
--- DesigningDigits.Com ---
--- Corey@CoreySchafer.com----
-------------------------------------------
/*
Version 1.0 - Inital
Removes all alerts created by this script if they exist.
Create alerts for Errors 16 through 25
Create Alerts for Error 825
Create Alerts for ALL log growth past 70 - to DBA
Create Alerts for ALL log growth at 90 - to DBA Group
Create Alerts for ALL log growth past 90 - to DBACritical
Create Alerts for ALL CPU Usage past 70 - to DBA
Create Alerts for ALLCPU Usage at 90 - to DB Group
Create Alerts for ALLCPU Usage pat 90 - to DBACritical
Create Alerts for Long Transaction
Create Alerts for TempDB 70,80 to DBA
Create Alerts for TempDB 90 to DBA Group
Create Alerts for TempDB 95, 100 to DBACritical
Created backwards compatibility for 2008 and earlier.
*/
-----------------------------------------
-- REMOVE ALL OLD Alerts
-----------------------------------------
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 16 Alert - Misc User Error'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 16 Alert - Misc User Error'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 17 Alert - Insufficient Resources'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 17 Alert - Insufficient Resources'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 18 Alert - Nonfatal Internal Error Detected'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 18 Alert - Nonfatal Internal Error Detected'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 19 Alert - SQL Fatal Error in Resource'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 19 Alert - SQL Fatal Error in Resource'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 20 Alert - SQL Fatal Error in Current Process'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 20 Alert - SQL Fatal Error in Current Process'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 21 Alert - SQL Fatal Error in Database dbid Processes'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 21 Alert - SQL Fatal Error in Database dbid Processes'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 22 Alert - SQL Fatal Error Table Integrity Suspect'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 22 Alert - SQL Fatal Error Table Integrity Suspect'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 23 Alert - SQL Fatal Error Database Integrity Suspect'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 23 Alert - SQL Fatal Error Database Integrity Suspect'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = 'Error 24 Alert - Hardware Error'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 24 Alert - Hardware Error'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 25 Alert - Hardware Issue'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 25 Alert - Hardware Issue'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Error 825 Alert - ReadOnly Issues'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Error 825 Alert - ReadOnly Issues'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Performance-CPU 080 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Performance-CPU 080 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Performance-CPU 090 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Performance-CPU 090 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Performance-CPU 095 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Performance-CPU 095 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Performance-CPU 100 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Performance-CPU 100 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Performance-Long Transaction'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Performance-Long Transaction'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Tempdb - 070 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Tempdb - 070 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Tempdb - 080 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Tempdb - 080 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Tempdb - 090 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Tempdb - 090 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Tempdb - 095 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Tempdb - 095 Percent Usage'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Tempdb - 100 Percent Usage'))
EXECUTE msdb.dbo.sp_delete_alert @name = N'Tempdb - 100 Percent Usage'
--Remove Log Alerts
SET NOCOUNT ON;
DECLARE @Dbname nvarchar(100), @SQLString nvarchar(max), @jobname varchar(500)
DECLARE db_cursor CURSOR FOR
select db.name AS DBName FROM
sys.master_files mf
INNER JOIN
sys.databases db ON db.database_id = mf.database_id
where type_desc = 'LOG' and db.name not in ('TempDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @jobname = 'LogFileAbove70Percent (' + @Dbname + ')'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @jobname))
EXECUTE msdb.dbo.sp_delete_alert @name =@jobname
set @jobname = 'LogFileAbove90Percent (' + @Dbname + ')'
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @jobname))
EXECUTE msdb.dbo.sp_delete_alert @name =@jobname
FETCH NEXT FROM db_cursor INTO @Dbname
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
-- CREATES OPERATORS IF THEY DO NOT EXIST
IF (NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators where name = 'Corey Schafer'))
EXEC msdb.dbo.sp_add_operator @name = 'Corey Schafer', @enabled = 1, @email_address = 'cschafer@nutracorp.com';
IF (NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators where name = 'DBAdminCritical'))
EXEC msdb.dbo.sp_add_operator @name=N'DBAdminCritical', @enabled=1, @email_address=N'DBAdminCritical@nutracorp.com'
IF (NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators where name = 'DBAdmin'))
EXEC msdb.dbo.sp_add_operator @name=N'DBAdmin', @enabled=1, @email_address=N'DBAdmin@nutracorp.com'
--CRITICAL ALERTS
EXEC msdb.dbo.sp_add_alert @name=N'Error 16 Alert - Misc User Error',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 17 Alert - Insufficient Resources',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 18 Alert - Nonfatal Internal Error Detected',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 19 Alert - SQL Fatal Error in Resource',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 20 Alert - SQL Fatal Error in Current Process',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 21 Alert - SQL Fatal Error in Database dbid Processes',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 22 Alert - SQL Fatal Error Table Integrity Suspect',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 23 Alert - SQL Fatal Error Database Integrity Suspect',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 24 Alert - Hardware Error',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 25 Alert - Hardware Issue',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 825 Alert - ReadOnly Issues',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N''
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 16 Alert - Misc User Error',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 825 Alert - ReadOnly Issues',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 17 Alert - Insufficient Resources',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 18 Alert - Nonfatal Internal Error Detected',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 19 Alert - SQL Fatal Error in Resource',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 20 Alert - SQL Fatal Error in Current Process',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 21 Alert - SQL Fatal Error in Database dbid Processes',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 22 Alert - SQL Fatal Error Table Integrity Suspect',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 23 Alert - SQL Fatal Error Database Integrity Suspect',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 24 Alert - Hardware Error',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 25 Alert - Hardware Issue',
@operator_name = 'Corey Schafer',
@notification_method = 1;
GO
--Get Version Information for Syntax for Upcoming Areas
declare @VersionNumber varchar(100)
declare @MajorReleaseNumber bigint
set @VersionNumber = convert(varchar, SERVERPROPERTY('productversion'))
set @MajorReleaseNumber=substring(@VersionNumber, 1, patindex('%.%', @VersionNumber)-1)
--- TEMP DB ALERTS
declare @TempDBOjbectName varchar(200)
declare @filterValue varchar(10)
declare @FilterString varchar(210)
if @MajorReleaseNumber >= 12
begin
set @TempDBOjbectName = 'Databases|Percent Log Used|tempdb|>|'
end
else
begin
set @TempDBOjbectName = 'SQLServer:Databases|Percent Log Used|tempdb|>|'
end
set @filterValue = 70
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Tempdb - 070 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
set @filterValue = 80
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Tempdb - 080 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
set @filterValue = 90
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Tempdb - 090 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'', @performance_condition=@FilterString
set @filterValue = 95
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Tempdb - 095 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
set @filterValue = 100
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Tempdb - 100 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
EXEC msdb.dbo.sp_add_notification @alert_name=N'Tempdb - 070 Percent Usage', @operator_name=N'Corey Schafer', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Tempdb - 080 Percent Usage', @operator_name=N'Corey Schafer', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Tempdb - 090 Percent Usage', @operator_name=N'DBAdmin', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Tempdb - 095 Percent Usage', @operator_name=N'DBAdminCritical', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Tempdb - 100 Percent Usage', @operator_name=N'DBAdminCritical', @notification_method = 1
--- CPU Alerts
if @MajorReleaseNumber > 9 begin
if @MajorReleaseNumber >= 12
begin
set @TempDBOjbectName = 'Resource Pool Stats|CPU usage %|default|>|'
end
else
begin
set @TempDBOjbectName = 'SQLServer:Resource Pool Stats|CPU usage %|default|>|'
end
set @filterValue = 80
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Performance-CPU 080 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
set @filterValue = 90
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Performance-CPU 090 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
set @filterValue = 95
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Performance-CPU 095 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
set @filterValue = 100
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Performance-CPU 100 Percent Usage',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@database_name=N'',
@performance_condition=@FilterString
EXEC msdb.dbo.sp_add_notification @alert_name=N'Performance-CPU 080 Percent Usage', @operator_name=N'Corey Schafer', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Performance-CPU 090 Percent Usage', @operator_name=N'DBAdmin', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Performance-CPU 095 Percent Usage', @operator_name=N'DBAdminCritical', @notification_method = 1
EXEC msdb.dbo.sp_add_notification @alert_name=N'Performance-CPU 100 Percent Usage', @operator_name=N'DBAdminCritical', @notification_method = 1
END
-- Setup Log Files for TempDB 70% and 90%
SET NOCOUNT ON;
DECLARE @Dbname nvarchar(100)
Declare @SQLString nvarchar(max)
DECLARE db_cursor CURSOR FOR
select db.name AS DBName FROM
sys.master_files mf
INNER JOIN
sys.databases db ON db.database_id = mf.database_id
where type_desc = 'LOG' and db.name not in ('TempDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @SQLString = 'msdb.dbo.sp_add_alert @name=N''LogFileAbove70Percent (' + @Dbname + ')'',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@category_name=N''[Uncategorized]'',
@performance_condition=N''SQLServer:Databases|Percent Log Used|' + @Dbname + '|>|70'',
@job_id=N''00000000-0000-0000-0000-000000000000'''
exec(@SQLString)
set @SQLString = 'EXEC msdb.dbo.sp_add_notification
@alert_name = N''LogFileAbove70Percent (' + @Dbname + ')'',
@operator_name = ''Corey Schafer'',
@notification_method = 1;'
exec(@SQLString)
set @SQLString = 'msdb.dbo.sp_add_alert @name=N''LogFileAbove90Percent (' + @Dbname + ')'',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@category_name=N''[Uncategorized]'',
@performance_condition=N''SQLServer:Databases|Percent Log Used|' + @Dbname + '|>|90'',
@job_id=N''00000000-0000-0000-0000-000000000000'''
exec(@SQLString)
set @SQLString = 'EXEC msdb.dbo.sp_add_notification
@alert_name = N''LogFileAbove90Percent (' + @Dbname + ')'',
@operator_name = ''DBAdminCritical'',
@notification_method = 1;'
exec(@SQLString)
FETCH NEXT FROM db_cursor INTO @Dbname
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
--LONG TRANSACTIONS
if @MajorReleaseNumber >= 12
begin
set @TempDBOjbectName = 'Transactions|Transactions||>|'
end
else
begin
set @TempDBOjbectName = 'SQLServer:Transactions|Transactions||>|'
end
set @filterValue = 5000
set @FilterString = @TempDBOjbectName + @filterValue
EXEC msdb.dbo.sp_add_alert @name=N'Performance-Long Transaction',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message=N'Longest Transaction',
@category_name=N'[Uncategorized]',
@performance_condition=@FilterString
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Performance-Long Transaction', @operator_name=N'Corey Schafer', @notification_method = 1
GO
Comments