--Creating DeadLock Scenario
--step1:
create table emps(empid int,ename varchar(40),sal money)
create table depts(deptno int,dname varchar(40))
--step2:
insert into emps values(1,'Mahesh',45000)
insert depts values(10,'sales')
--step3: Take new query
--user1
begin tran
       update emps set sal=3500 where empid=1
--step4: Take new query
--user2
begin tran
       update depts set dname='IT' where deptno=10
       delete from emps where empid=1
--step5: Go to user1 query
       delete from depts where deptno=10
--step6: To capture deadlocks info into errorlog
dbcc traceon(3605,1204,-1)
--step7: Go to the session which was completed and rollback and repeat the same steps
--step8: Go to Current errorlog..check the deadlock details
--Creating alert and job to capture deadlock details
--======================================================
USE [msdb];
GO
IF OBJECT_ID(N'WMI_DeadlockEvents') IS NOT NULL
BEGIN
    DROP TABLE [WMI_DeadlockEvents];
END
GO
-- Create a table to store the deadlock graphs
CREATE TABLE [WMI_DeadlockEvents]
(
       [RowID] INT IDENTITY PRIMARY KEY,
       [DeadlockGraph] XML,
       [CollectionDate] DATETIME DEFAULT(CURRENT_TIMESTAMP)
) ;
GO

-- Enable job tokens in SQL Agent
EXEC [sp_set_sqlagent_properties]
              @alert_replace_runtime_tokens=1
GO

-- Restart Agent

-- Create a job for the Agent alert to execute
EXECUTE [sp_add_job]
       @job_name=N'capture_dealocks',
    @enabled=1,
    @description=N'Captures DEADLOCK_GRAPH events raised by SQL Agent alerts to a table';
GO

-- Add a jobstep to insert the grafph from WMI
EXECUTE [sp_add_jobstep]
    @job_name = N'capture_dealocks',
    @step_name=N'Insert graph into WMI_DeadlockEvents',
    @step_id=1,
    @on_success_action=1,
    @on_fail_action=2,
    @subsystem=N'TSQL',
    @command= N'INSERT INTO WMI_DeadlockEvents
                (DeadlockGraph)
                VALUES (N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
    @database_name=N'msdb';
GO
-- Set the job server for the job
EXECUTE [sp_add_jobserver]
       @job_name = N'capture_dealocks';
GO
--Add a WMI alert for the DEADLOCK_GRAPH
EXECUTE [sp_add_alert]
       @name=N'DeadLocks_Alerts',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\HYDSBI01',
    @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
    @job_name=N'capture_dealocks';
GO
--Check the deadlock details
use msdb
go
select * from WMI_DeadlockEvents