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
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.