SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ooops! Was that me? (Blog Challenge)

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

 

SQLEspresso

I am Monica Rathbun. I’m currently a Sr. Database Administrator/BI Architect at Massimo Zanetti Beverage, USA in Virginia. I’ve been a Lone DBA for 15 years, working with all aspects of SQL Server and Oracle. I am currently the co-leader for the Hampton Roads SQL Server User Group and can be found on Twitter daily as @SQLEspresso. I am passionate about SQL Server and the #SQLFamily, doing anything I can to give back to such a wonderful community. As a new speaker at SQL Saturdays and a new blogger, I hope to earn my place as a valued member. When I’m not busy with work, you will find me playing taxi to my two daughters back and forth to dance classes.

Comments

Leave a comment on the original post [sqlespresso.com, opens in a new window]

Loading comments...