Home » SQL Server 2008 Extended Events 3

SQL Server 2008 Extended Events 3

sql server DBA 999

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

Leave a Reply

Your email address will not be published. Required fields are marked *