Can I get notification when event occur?

SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example,
If I decide to monitor any dead lock in the database, I just query the trace file to see the history of deadlock for a period of time. Here is my question:

While events occur, in our example deadlock event, is there a way to get an email notification using msdb.dbo.xp_send_dbmail?

Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here.

Below is the script that will help you with Deadlock detection and email in real time :

It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to replace required stuff.

```
use dba_db ---- change HERE use find and replace as per your database name !!
go
/****
Job Name : Monitoring - Deadlock Detector
Created by : Kin for dba.stackexchange.com
Version : V1.0
Funtionality: 1. Real time Deadlock detection using WMI
2. Send email out when deadlock occurs along with
Deadlock Graph attached.
3. Logs all the deadlocks in a central Table
dba_db.dbo.Deadlock
4. Does not require trace flags 1204 and 1222 enabled
*****/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Deadlock]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Print ' Table Deadlock Table Already Exists... moving Further '
ELSE
create table Deadlock
(
RecordId int identity (1,1) primary key not null,
AlertTime datetime not null,
DeadlockGraph xml,
Notified int not null constraint [DF_deadlock_flag] default (0)
)
go
create index deadlock_idx on Deadlock (AlertTime) with fillfactor = 100
go
USE [msdb]
GO
-- No need to enable deadlock trace flags
--dbcc traceon (1204,1222,-1)
--go
-- enable replace runtime tokens for sql agent to respond to WMI alets
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
-- create the job
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitoring - Deadlock Detector',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job Name : Monitoring - Deadlock Detector
Created by : Kin for dba.stackexchange.com
Version : V1.0
Funtionality: 1. Real time Deadlock detection using WMI
2. Send email out when deadlock occurs along with
Deadlock Graph attached.
3. Logs all the deadlocks in a central Table
dba_db.dbo.Deadlock
4. Does not require trace flags 1204 and 1222 enabled',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Deadlock info',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO Deadlock (

AlertTime,

DeadlockGraph

)

VALUES (

GETDATE(),

''$(ESCAPE_NONE(WMI(TextData)))''

)',
@database_name=N'dba_db',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [Send Email with Deadlock Graph] Script Date: 10/01/2010 12:01:45 /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email with Deadlock Graph',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'if exists (select 1 from dba_db.dbo.Deadlock where notified = 0 )
begin
declare @tableHTML nvarchar(max)
set @tableHTML =N''Deadlock Has occured on ''+@@servername+'' .. Please Investigate Immediately ''
set @tableHTML = @tableHTML+ N'''' +
N'''' +
N''RecordId'' +
N''AlertTime'' +
N''DeadlockGraph'' +

N'''' +
ISNULL(CAST ( (
select td = '''',
td = RecordId,'''',
td = AlertTime,'''',
td = DeadlockGraph,''''

from dba_db.dbo.Deadlock where notified = 0

FOR XML PATH(''tr''), TYPE

) AS NVARCHAR

StackExchange Database Administrators Q#41614, answer score: 6