SQL Server Agent Alerts for Critical Errors

Introduction

One part of being a “good” DBA is to configure your SQL Server instances so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server. It continues with what you do over time to maintain and monitor your SQL Server instances. Adding SQL Server Agent Alerts for critical errors is an important step!

I previously wrote a three part series about how to provision a new SQL Server instance that is available on the SimpleTalk web site. Part One is here, Part Two is here, and Part Three is here. If you read and follow my recommendations in those three articles, you will have a very good foundation.

SQL Server Agent Alerts

One subject that I touched on in Part Three of that series was how to use some T-SQL to create SQL Server Agent Alerts for a number of critical error conditions. A SQL Agent Alert will fire when certain types of errors occur, and you can configure it to notify a SQL Operator when this happens. The SQL Operator can be associated with an email distribution list. As a result, you will find out about these types of problems much earlier than you normally would. This is a good thing!

The code in this script is an updated version that gets the server name and uses that in the the alert name. In addition, the script also uses variables for the operator name and the alert category name. I have added an additional alert for Error 832, and for Errors 855 and 856 (if you have SQL Server 2012 Enterprise Edition or newer).

Memory Error Recovery

SQL Server 2012 Enterprise Edition added a new feature called Memory Error Recovery. It can detect (and sometimes correct) when clean database pages have become corrupted in memory. You need to have Windows Server 2012 or newer, and you must have a new enough processor. This would be an Intel Xeon 7500 series or newer that supports this functionality. If you have all of this, you will see this message in your SQL Server Error Log:

Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.

There are more details in this KB article: Using SQL Server in Windows 8 and Windows Server 2012 environments. There is also a PowerPoint deck about this feature here (on slides 11 and 12).

Final Words

In this script, the delay between responses is set to 900 seconds, which is 15 minutes. By default, I did not want the operator to get spammed with e-mail alerts. You can always adjust this value to whatever you see fit. You will need to supply a valid SQL Operator name for my script to work correctly.

I also have some additional scripts on my Resources page that are very useful for configuring SQL Server instances.