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.
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
and administrator automatically receives an email like this.