Creating a SQL Server Agent Alert by Using the WMI Provider for Server Events

USE AdventureWorks ; GO  IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL BEGIN     DROP TABLE DeadlockEvents ; END ; GO  CREATE TABLE DeadlockEvents     (AlertTime DATETIME, DeadlockGraph XML) ; GO -- Add a job for the alert to run.  EXEC  msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',      @enabled=1,      @description=N'Job for responding to DEADLOCK_GRAPH events' ; GO  -- Add a jobstep that inserts the current time and the deadlock graph into -- the DeadlockEvents table.  EXEC msdb.dbo.sp_add_jobstep     @job_name = N'Capture Deadlock Graph',     @step_name=N'Insert graph into LogEvents',     @step_id=1,      @on_success_action=1,      @on_fail_action=2,      @subsystem=N'TSQL',      @command= N'INSERT INTO DeadlockEvents                 (AlertTime, DeadlockGraph)                 VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',     @database_name=N'AdventureWorks' ; GO  -- Set the job server for the job to the current instance of SQL Server.  EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ; GO  -- Add an alert that responds to all DEADLOCK_GRAPH events for -- the default instance. To monitor deadlocks for a different instance, -- change MSSQLSERVER to the name of the instance.  EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',      @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',      @job_name='Capture Deadlock Graph' ; GO

-----------------------------------------

Add Notification  step with attachment step:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'DBAMAIL',

    @recipients = 'saif_qazi0532@hotmail.com',

    @query = 'SELECT * FROM OmniCourt..DeadlockEvents' ,

    @subject = 'Capture Deadlock Graph on testdb@A server ',

    @attach_query_result_as_file = 1,

    @query_result_width = '32767' ,

@query_no_truncate = 1 ;

------------------------------------------END--------------------------------

If  Capture Deadlock Graph JOB Failed as below error getting 

Error Message

Unable to start execution of step 1 (reason: Variable WMI(TextData) not found).  The step failed.

Solution: Right Click Agent Property >>check replace token  >click ok button after that re-run the SQL AGENT SERVICE. I hope issue will resolved. 

Testing the Sample

To see the job run, provoke a deadlock. In SQL Server Management Studio, open two SQL Query tabs and connect both queries to the same instance. Run the following script in one of the query tabs. This script produces one result set and finishes

Example:

-- 1) Create Objects for Deadlock

USE TEMPDB

CREATE TABLE dbo.foo (col1 INT)

INSERT dbo.foo SELECT 1

CREATE TABLE dbo.bar (col1 INT)

INSERT dbo.bar SELECT 1

-- 2) Run in first connection and the server this connection was SPID 59

BEGIN TRAN

UPDATE tempdb.dbo.foo SET col1 = 1

-- 3) Run in second connection and the server this connection was SPID 62

BEGIN TRAN

UPDATE tempdb.dbo.bar SET col1 = 1

UPDATE tempdb.dbo.foo SET col1 = 1

-- 4) Run in first connection

UPDATE tempdb.dbo.bar SET col1 = 1