Now lets create a New Event Session to capture Deadlock Victims
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Demo_session_Deadlock')
DROP EVENT SESSION Demo_session_Deadlock ON SERVER
GO
-- CREATES THE SESSION
CREATE EVENT SESSION Demo_session_Deadlock
ON SERVER
ADD EVENT
sqlserver.lock_deadlock
( ACTION ( sqlserver.database_id,
sqlserver.client_app_name,
sqlserver.sql_text ) )
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'G:Datadata2.xel' , metadatafile = 'G:Datadata2.xem')
GO
-- STARTS THE SESSION
ALTER EVENT SESSION Demo_session_Deadlock ON SERVER STATE = START
GO
Now create a deadlock by using some updates
--DeadLock Simulation
--First Session
begin tran
update [Demo1] set [Column1] = 'abcd'
/*
--Second Session, Run this from Another window
begin tran
update [Demo2] set [column1] = 'abcd'
update [Demo1] set [Column2] = 'abcd'
--commit transaction
Now Run below code after second session is ran from first session
update [Demo1] set [Column1] = 'abcd'
*/
---commit transaction
Now Deadlock victim should be declared.
Now Cleanup the things if you like
-- STOPS LISTENING FOR THE EVENT
ALTER EVENT SESSION Demo_session_Deadlock ON SERVER
STATE = STOP
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
DROP EVENT SESSION Demo_session_Deadlock ON SERVER
GO
-- REMOVES THE EVENT SESSION FROM THE SERVER
Now read the Files to see the Deadlock queries captured
select CAST(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file
('G:Datadata2*.xel','G:Datadata2*.xem', null, null)
Output XML File will look below.
output_Xml