Create Standard SQL Agent Alerts For SQL Server

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