We have all made mistakes in our careers, I thought I’d share one of mine as a quick tip to others so that you don’t make the same one.
Everyone has their SQL Alerts setup right? If not, I have included the script below and here is the MSDN link to find out more (https://msdn.microsoft.com/en-us/library/ms180982.aspx).
For those who have setup their alerts, how many of you have remembered to set the DELAY BETWEEN RESPONSES setting?
When I worked at the Port of Virginia, I was a little less experienced in SQL and didn’t notice this lovely little option. I of course failed to set it. Can anyone guess what happened? YEP, we got low on resources in the wee hours of the morning and SQL kicked off an Error 017-Insufficient Resources. Thousands of emails were generated and caused the Exchange server to go down as well as some other issues that arose because of this. The worst part is that all the emails had to finish processing before we could delete them from the system. I think when all was said and done there was well over 250k messages it created.
So the morale of the story is, pay attention to this little tiny option when you set up your alerts your Exchange Admin will thank you for it.
Blog Challenge
Do you have a “Oops was that me” story to tell? If so, share it using hash tag #sqlmistakes. Link back to this blog, so we can all learn from each other. I can’t wait to hear your stories.
Create Alert Script
USE [msdb] GO /****** Object: Alert [017- Insufficient Resources] Script Date: 12/22/2016 9:01:14 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'017- Insufficient Resources', @message_id=0, @severity=17, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [018- Nonfatal Internal Error] Script Date: 12/22/2016 9:01:18 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'018- Nonfatal Internal Error', @message_id=0, @severity=18, @enabled=1, @delay_between_responses=120, @include_event_description_in=0, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [019- Fatal Error in Resource] Script Date: 12/22/2016 9:01:25 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'019- Fatal Error in Resource', @message_id=0, @severity=19, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [020- Fatal Error in Current Process] Script Date: 12/22/2016 9:01:30 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'020- Fatal Error in Current Process', @message_id=0, @severity=20, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [021- Fatal Error in Database Processes] Script Date: 12/22/2016 9:01:35 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'021- Fatal Error in Database Processes', @message_id=0, @severity=21, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [022- Fatal Error: Table Integrity Suspect] Script Date: 12/22/2016 9:01:40 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'022- Fatal Error: Table Integrity Suspect', @message_id=0, @severity=22, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [023- Fatal Error: Database Integrity Suspect] Script Date: 12/22/2016 9:01:45 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'023- Fatal Error: Database Integrity Suspect', @message_id=0, @severity=23, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [024- Fatal Error: Hardware] Script Date: 12/22/2016 9:01:50 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'024- Fatal Error: Hardware', @message_id=0, @severity=24, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [025- Fatal Error] Script Date: 12/22/2016 9:01:56 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'025- Fatal Error', @message_id=0, @severity=25, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [2570 - Data Purity Error] Script Date: 12/22/2016 9:02:01 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'2570 - Data Purity Error', @message_id=2570, @severity=0, @enabled=1, @delay_between_responses=120, @include_event_description_in=0, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [823- Read/Write Failure] Script Date: 12/22/2016 9:02:06 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'823- Read/Write Failure', @message_id=823, @severity=0, @enabled=1, @delay_between_responses=120, @include_event_description_in=0, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [824- Data Retriveal SAN Slowdown Page Error] Script Date: 12/22/2016 9:02:10 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'824- Data Retriveal SAN Slowdown Page Error', @message_id=824, @severity=0, @enabled=1, @delay_between_responses=120, @include_event_description_in=1, @notification_message=N'SQL Server has actually tried that I/O a total of 4 times before it finally declares a lost cause', @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [825- I/O subsystem is going wrong Read-Retry Required] Script Date: 12/22/2016 9:02:15 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'825- I/O subsystem is going wrong Read-Retry Required', @message_id=825, @severity=0, @enabled=1, @delay_between_responses=120, @include_event_description_in=0, @category_name=N'[Uncategorized]' GO USE [msdb] GO /****** Object: Alert [833- In Memory Check Sum Failure] Script Date: 12/22/2016 9:02:20 AM ******/ EXEC msdb.dbo.sp_add_alert @name=N'833- In Memory Check Sum Failure', @message_id=833, @severity=0, @enabled=1, @delay_between_responses=120, @include_event_description_in=0, @category_name=N'[Uncategorized]' GO
