Setting up Email Alerts for SQL Server Deadlocks

Step #1: Configuring Database Mail 

---STEP 2

EXEC master.sys.sp_altermessage 1205, 'WITH_LOG', TRUE;

GO

EXEC master.sys.sp_altermessage 3928, 'WITH_LOG', TRUE;

GO

---STEP3

DBCC TRACEON (1204, -1)

DBCC TRACEON (1222,-1)

Step #3 Configure alerts and notifications operator

*alert 1 for 1205:

USE [msdb]

GO

/****** Object:  Alert [Error #1205: Deadlock detected] ******/

EXEC msdb.dbo.sp_add_alert @name=N'Error #1205: Deadlock detected', 

@message_id=1205, 

@severity=0, 

@enabled=1, 

@delay_between_responses=0, 

@include_event_description_in=1, 

@database_name=N'APTECH', 

@category_name=N'[Uncategorized]', 

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

*alert 2 for #3928: 

USE [msdb]

GO

/****** Object:  Alert [Error #3928: Deadlock detected]  ******/

EXEC msdb.dbo.sp_add_alert @name=N'Error #3928: Deadlock detected', 

@message_id=3928, 

@severity=0, 

@enabled=1, 

@delay_between_responses=0, 

@include_event_description_in=1, 

@database_name=N'APTECH', 

@category_name=N'[Uncategorized]', 

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

-----------for Notification create operator

USE [msdb]

GO

EXEC msdb.dbo.sp_add_operator @name=N'SQLDBA', 

@enabled=1, 

@weekday_pager_start_time=90000, 

@weekday_pager_end_time=180000, 

@saturday_pager_start_time=90000, 

@saturday_pager_end_time=180000, 

@sunday_pager_start_time=90000, 

@sunday_pager_end_time=180000, 

@pager_days=0, 

@email_address=N'mumdbateam@gmail.com;banshbasti@gmail.com', 

@category_name=N'[Uncategorized]'

GO

-----Add Alert Option Response Options:-#1205: 

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error #1205: Deadlock detected',

 @operator_name=N'SQLDBA', @notification_method = 1

Go

--------------Add Alert Option Response Options:-#3928: 

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error #1205: Deadlock detected',

 @operator_name=N'SQLDBA', @notification_method = 1

Go

-------for testing purpuse table creation 

CREATE TABLE EMPDETAILS(EMPID INT)

INSERT EMPDETAILS SELECT 1

CREATE TABLE EMPDETAILS2 (EMPID INT)

INSERT EMPDETAILS2 SELECT 1

--Tran2 in new session

BEGIN TRAN

UPDATE  EMPDETAILS1 SET  ID= 1

--Tran3

BEGIN TRAN

UPDATE  EMPDETAILS2 SET  ID= 1

UPDATE  EMPDETAILS1 SET  ID= 1

--Let's update Demodeadlock2 table in our Tran2 session

UPDATE  EMPDETAILS2 SET  ID= 1

Step #1: Configuring Database Mail