Automated notifications on SQL Server errors

Can SQL Server send a notification to me when a deadlock is occurred? Can SQL Server alert me when a database starts suffering with resource issues? I am sure that you have already gone through these type of requirements and I am sure that you have already implemented a mechanism for getting alerts. However, how it has been implemented? Using set of codes? Using Agent Jobs? You may have used a different approach but that is what I have seen with many, nothing wrong with it unless you need more details, run through some logic and have a great flexibility. If all you want is, just to get an "Alert", why do not you use "SQL Server Alerts"?

SQL Server Alerts

SQL Server Alert is a predefined response to an event. You can create an alert configuring it as a "SQL Server event alert", "SQL Server performance condition alert", or "WMI event alert". In order to create an alert for errors, "SQL Server event alert" should be selected. It allows us to create a response to individual error or to all errors of a specific severity level. For example, if you want to get an alert on file growth issues, you can create the Alert for error number 1105 which come under 17 severity level. If you want to get this alert not only for 1105 but for all errors that come under 17, create the alert for severity 17. Here is the way of creating an alert for 1105;

Once the alert is created, an action has to be added for responding to the event. There are two types of actions that can be configured; Execute a Job, Notify Operators. Since the post is speaking about notifications, let's see how to configure the action for notifying a person.

SQL Server Operators

The easiest way of notifying a person is, setting it up via an operator. The SQL Server Agent Operator is a person or group that can receive notifications from an alert or job. Let's create an operator.

As you see, "Database Administrator" operator has been configured with "Email name" notification option. Note that, in order to send emails by SQL Server, Database Mail has to be configured. Let's go back to the Alert and configure the "Response".

We need to do one more configuration for receiving alerts. You need to enable "Enable mail profile" of "Alert System" that comes under SQL Server Agent properties.

All okay now. Let's test and see whether administrator gets the error via an email. Below code creates a database with 4mb size data file and disables "File Growth" of it. Next it inserts bunch of records until the data file gets fully filled. Once the data file is full, SQL Server throws an error with 1105.

-- Create new DB called 'AlertTestDB"
USE master;
GO
CREATE DATABASE [AlertTestDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AlertTestDB'
 , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AlertTestDB.mdf' 
 , SIZE = 4096KB 
 , MAXSIZE = UNLIMITED
 , FILEGROWTH = 0)
 LOG ON 
( NAME = N'AlertTestDB_log'
 , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AlertTestDB_log.ldf' 
 , SIZE = 1024KB 
 , MAXSIZE = 2048GB 
 , FILEGROWTH = 10%)
GO

-- Connnect and create a table
USE AlertTestDB;
GO

CREATE TABLE TestTable
(
 Id int identity(1,1) PRIMARY KEY
 , Column1 char(2000)
);
GO

-- Insert record until Sever throws an error
SET NOCOUNT ON;

WHILE 1=1
BEGIN

 INSERT INTO TestTable (Column1) VALUES ('test data');
END;
GO

Once the code is executed, following error will be thrown

and administrator automatically receives an email like this.