SQL Server Severity Alerts

Severity Alerts can help you be proactive on the health of your server.

Check and run this Script to set up in your SQL server.

• Research on current alerts that may do the same thing and operators
  select name from msdb.dbo.sysalerts
  where name not in
  (select name from msdb.dbo.sysalerts
  where name like ‘%1_%’ OR name like ‘%2_%’)

select top 1 name, email_address from msdb.dbo.sysoperators where lower(email_address) in (‘Add your Email’, ‘Add your Email’, ‘Add your Email’)
*/

/* DROPPING EXISTING ALERTS THAT MATCH THE QUERY STRING
SET NOCOUNT ON
GO
CREATE TABLE #ALERTNAME
(name VARCHAR(100))

— ALERTS IN THE TEMP TABLE WILL BE DROPPED
INSERT #ALERTNAME
SELECT name FROM msdb.dbo.sysalerts
WHERE name LIKE ‘%1_%’ OR name LIKE ‘%2_%’

DECLARE @ALERTNAME VARCHAR(50)
,@CMD VARCHAR(256)

WHILE (SELECT COUNT(*) FROM #ALERTNAME) > 0
BEGIN
SET @ALERTNAME = (SELECT TOP 1 name FROM #ALERTNAME)
SET @CMD = ‘msdb.dbo.sp_delete_alert @name = ”’ + @ALERTNAME + ””
DELETE FROM #ALERTNAME WHERE name = @ALERTNAME
PRINT(@CMD)
–EXEC(@CMD)
END

DROP TABLE #ALERTNAME

*/

USE msdb;
GO
/*
The severity level are displayed in the table below.

0-10 Messages with a severity level of 0 to 10 are informational messages and not actual errors.

11-16 Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user.
For example, an error message for an invalid update query, will have a severity level of 16.

17 Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks.
Severity error 17 can be corrected by the DBA, and in some cases, by the database owner.

18 Severity level 18 messages indicate nonfatal internal software problems.

19 Severity level 19 indicates that a nonconfigurable resource limit has been exceeded.

20 Severity level 20 indicates a problem with a statement issued by the current process.

21 Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes
in a database.

22 Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem,
stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the
problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take.

23 Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action
to take, use the DBCC commands.

24 Severity level 24 indicates a hardware problem.

25 Severity level 25 indicates some type of system error.

• */

DECLARE @OPERATOR SYSNAME
SET @OPERATOR = (select top 1 name from msdb.dbo.sysoperators where lower(email_address) in (‘Add your Email’, ‘Add your Email’, ‘Add your Email’))
PRINT @OPERATOR

IF @OPERATOR IS NOT NULL
BEGIN
EXEC msdb.dbo.sp_add_alert @name=N’Full log file error 9002′,
@message_id=9002,
@severity=0,
@enabled=1,
@delay_between_responses=10,
@include_event_description_in=5,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′

EXEC msdb.dbo.sp_add_alert @name = N'825 - Read-Retry Required', @message_id = 825,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;

EXEC msdb.dbo.sp_add_notification @alert_name = N'825 - Read-Retry Required',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 16', @message_id = 0,
    @severity = 16, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 16',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 17', @message_id = 0,
    @severity = 17, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 17',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 18', @message_id = 0,
    @severity = 18, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 18',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 19', @message_id = 0,
    @severity = 19, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 19',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 20', @message_id = 0,
    @severity = 20, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 20',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 21', @message_id = 0,
    @severity = 21, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 21',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 22', @message_id = 0,
    @severity = 22, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 22',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 23', @message_id = 0,
    @severity = 23, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 23',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 24', @message_id = 0,
    @severity = 24, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 24',
    @operator_name = @OPERATOR, 
    @notification_method = 7

EXEC msdb.dbo.sp_add_alert @name = N'Severity 25', @message_id = 0,
    @severity = 25, @enabled = 1, @delay_between_responses = 60,
    @include_event_description_in = 1

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 25',
    @operator_name = @OPERATOR, 
    @notification_method = 7

END
ELSE
RAISERROR(‘No Operator has been setup with an appropriate email address.’,16,1)