SQL Server Alerts

Question

SQL Server Alerts

Larry Pohner

Old Hand

Points: 332
More actions
August 8, 2006 at 1:22 pm

#172056

I am implementing SQL Server Alerts and is wondering if anyone out there has implemented them and has any input. There isn't a lot of information on the practicle use of alerts either on this site or on the web (either that or I'm not searching correctly).
Specifically, I'm wondering what errors I should key on and what performance counters. Any opinions are welcome.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

This can depend somewhat on your application, for example if you have a poor application with loads of deadlocks, you might what an alert on these. If you are worried about diskspace you might add an alert for this, if logs are filling etc... Below is a screen print of the standard alerts I usually add. The potentially fatal error Insufficient Resources is a must it covers diskspace and resource issues, I have an email alert on this as when it fires, you have a problem that needs urgent attention, I usually add email alerts as standard.

Regards

Carolyn

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 2

Carolyn, your image didn't show. Can you please re-post? Thanks.

Chris

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 3

I don't think reposting will help as I can see the image in my browser, I have scripted below. Report Problem is a job I have set up that fires in response to the alert being triggered and it emails me, the replication alerts will be created automatically when replication is set up:-

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, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Deadlock Occurance'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Deadlock Occurance', @message_id = 8901, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @job_name = N'Report Problem', @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, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error', @message_id = 0, @severity = 25, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error Database Integrity Suspect'

BEGIN

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

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error Hardware'

BEGIN

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

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fatal Error in Current Process'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in Current Process'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fatal Error in Current Process', @message_id = 0, @severity = 20, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in Database'

BEGIN

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

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error in Resource'

BEGIN

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

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fatal Error Table Integrity Suspect'

BEGIN

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

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Fragmentation above 50% in user index.'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Fragmentation above 50% in user index.'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Fragmentation above 50% in user index.', @message_id = 50001, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Full CGP Log'

BEGIN

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

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Full log'

BEGIN

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

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Full msdb log'

BEGIN

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

END

/* Start of Alert */

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

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Full tempdb'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Full tempdb', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'tempdb', @job_name = N'Report Problem', @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, @job_name = N'Report Problem', @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, @job_name = N'Report Problem', @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, @job_name = N'Report Problem', @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, @job_name = N'Report Problem', @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, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: agent custom shutdown'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: agent custom shutdown'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: agent custom shutdown', @message_id = 20578, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: agent failure'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: agent failure'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: agent failure', @message_id = 14151, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: agent retry'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: agent retry'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: agent retry', @message_id = 14152, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: agent success'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: agent success'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: agent success', @message_id = 14150, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: expired subscription dropped'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: expired subscription dropped'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: expired subscription dropped', @message_id = 14157, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: Subscriber has failed data validation'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: Subscriber has failed data validation'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: Subscriber has failed data validation', @message_id = 20574, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: Subscriber has passed data validation'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: Subscriber has passed data validation'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: Subscriber has passed data validation', @message_id = 20575, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

END

/* Start of Alert */

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Replication: Subscription reinitialized after validation failure'))

---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'Replication: Subscription reinitialized after validation failure'

BEGIN

EXECUTE msdb.dbo.sp_add_alert @name = N'Replication: Subscription reinitialized after validation failure', @message_id = 20572, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 5, @job_name = N'Report Problem', @category_name = N'Replication'

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, @database_name = N'CGP', @job_name = N'Report Problem', @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, @job_name = N'Report Problem', @category_name = N'[Uncategorized]'

END

Regards

Carolyn

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 4

CGSJohnson

SSCertifiable

Points: 7208

August 9, 2006 at 8:35 am

#654092

Thanks.