SQL Server Specialists :: Alerts for SQL Server 2000

This Month

Recent Articles

Find servers with fixed Ports on SQL Server

Script to Run DBCC CheckDB against all databases on an instance.

Maintenance Plan Fails with the following - Failed:(0) Alter failed for Server

SQLServerAgent cannot start because the instance of the server (MSSQLSERVER) is not the expected instance

Shrinking log issue - Unable to shrink

Error Handling in SQL 2005

Monitor for long running agent jobs

House Keeping - Delete old files using SSIS

Server Info

File Locations

List Databases on Server

Database Properties

SQL Server 2005 DDL Triggers

SQL Agent Error

Restore the Master Database

SQL Server Community Blogs

Month Archive

December 2007
November 2007
October 2007

Year Archive

2009
2008
2007

Main Page

Previous:	Save DTS packages as Structured Storage Files
Next:	How to transfer logins and passwords between instances of SQL Server

Alerts for SQL Server 2000

by Administrator on Thu 18 Oct 2007 22:37 BST | Permanent Link | Cosmos

-- These are our preferred Alerts for SQL Server 2000/2005

-- Author: SQL Server Specialists (www.SQLServerSpecialists.co.uk)

USE MSDB

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 19 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 19 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 20 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 20 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Sev. 21 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 21 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 22 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 22 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 23 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 23 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 24 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 24 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Sev. 25 Errors'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Sev. 25 Errors'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Demo: Full msdb log'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full msdb log'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full tempdb'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full tempdb'

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Backup Failure'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Backup Failure'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Backup Failure', @message_id = 3201, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Deadlock Event'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Deadlock Event'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Deadlock Event', @message_id = 1205, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Driver or Hardware problem with the I/O system'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Driver or Hardware problem with the I/O system'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Driver or Hardware problem with the I/O system', @message_id = 845, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error in current Proccess - 020'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in current Proccess - 020'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error in current Proccess - 020', @message_id = 0, @severity = 20, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error in Database Processes - 021'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in Database Processes - 021'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error in Database Processes - 021', @message_id = 0, @severity = 21, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error In Resource - 019'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error In Resource - 019'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error In Resource - 019', @message_id = 0, @severity = 19, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error: Database Integrity Suspect - 023'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Database Integrity Suspect - 023'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Database Integrity Suspect - 023', @message_id = 0, @severity = 23, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error: Hardware Error - 024'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Hardware Error - 024'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Hardware Error - 024', @message_id = 0, @severity = 24, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Error: Table Integrity Suspect - 022'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error: Table Integrity Suspect - 022'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error: Table Integrity Suspect - 022', @message_id = 0, @severity = 22, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Fatal Errror - 025'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Errror - 025'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Errror - 025', @message_id = 0, @severity = 25, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full Log - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full Log - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full Log - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full msdb log - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full msdb log - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full msdb log - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'msdb', @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Full tempdb - 9002'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Full tempdb - 9002'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full tempdb - 9002', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Lock Allocation'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Lock Allocation'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Lock Allocation', @message_id = 17125, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Lock request timeout period exceeded'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Lock request timeout period exceeded'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Lock request timeout period exceeded', @message_id = 1222, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Misc Table Error'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Misc Table Error'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Misc Table Error', @message_id = 2511, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Non Logged SQL Server Shutdown'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Non Logged SQL Server Shutdown'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Non Logged SQL Server Shutdown', @message_id = 20531, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF (EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Potentially Fatal Error Insufficient Resources'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Potentially Fatal Error Insufficient Resources'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Potentially Fatal Error Insufficient Resources', @message_id = 0, @severity = 17, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 4, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Unexpected failure acquiring application lock.'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Unexpected failure acquiring application lock.'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Unexpected failure acquiring application lock.', @message_id = 21414, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

/*Start of Alert*/

IF(EXISTS(SELECT name FROM msdb.dbo.sysalerts WHERE name= N'Unexpected failure releasing application lock'))

---- Delete the alert with the same name. EXECUTE msdb.dbo.sp_delete_alert @name = N'Unexpected failure releasing application lock'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Unexpected failure releasing application lock', @message_id = 21415, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'

END

Keywords: Alerts

Posted to:

Main Page

Scripts