SQL Server Alerts
-
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.
-
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
-
Carolyn, your image didn't show. Can you please re-post? Thanks.
Chris
-
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
-
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply