Ooops! Was that me? (Blog Challenge)

  By , , ,

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).

alert-list

For those who have setup their alerts, how many of you have remembered to set the DELAY BETWEEN RESPONSES setting?

alerts

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

oops

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

 

About Monica Rathbun

Monica Rathbun lives in Virginia, is a Microsoft MVP for Data Platform and Microsoft Certified Solutions Expert. She has nearly two decades of experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft Data Platform. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips. You can find Monica blogging at sqlespresso.com 
Tagged  , , , , .
Bookmark the permalink.

5 Comments

  2. Richard L Dawson
    January 11, 2017 at 1:03 pm

    Great post and reminder about the easy things to do to make our jobs easier. I did notice one thing though.

    There is a missing single quote at the end of your first alert script.

    Thanks again.
    Richard

  3. Pingback: Delay Between Responses – Curated SQL

  4. Pingback: Friday Reading 2017-01-13 | The DBA Who Came In From The Cold

Comments are closed