T-SQL: Create and Test an Alert

Step1 : create an operator who will receive notifications.

Use msdb
 
Go
 
If Not Exists(Select name From msdb.dbo.sysoperators
 
Where name = N'DBAdministrator')
 
Exec msdb.dbo.sp_add_operator @name=N'DBAdministrator',
 
@enabled=1,
 
@pager_days=0,
 
@email_address=N'account@mailserver.com'
 
Go

-- Step2 :

create an alert that triggers when a lack of resources.

If Not Exists(Select name From msdb.dbo.sysalerts
 
Where name = N'Lack of resources')
 
Exec msdb.dbo.sp_add_alert @name=N'Lack of resources',
 
@message_id=0,
 
@severity=17,
 
@enabled=1,
 
@delay_between_responses=0,
 
@include_event_description_in=1,
 
@job_id=N'00000000-0000-0000-0000-000000000000'
 
Go

-- Step3 :

notify the operator created previously.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name
 From msdb.dbo.sysnotifications as notifications
 
 inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
 
 inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
 
 Where alerts.name = N'Lack of resources'
 
 and operators.name = N'DBAdministrator'
 
 and  notification_method = 1)
 
 Exec msdb.dbo.sp_add_notification @alert_name=N'Lack of resources',
 
 @operator_name=N'DBAdministrator', @notification_method = 1
 
Go

-- Step 4 :

trigger alert.

Use [DataBase]
 
Go

Do not forget to use (With Log).

RaisError (N'An error occurred Severity 17: insufficient resources ! ', 17, 1)
 
With Log
 
Go

 Step 5 :

check your email. Troubleshooting

If the operator does not receive an e-mail
Test database mail by sending a test e-mail to the operator's e-mail address.
Make sure you restarted SQL Agent and it is running.