I'm trying to create historical log for deadlocks with the help of XEvents Session but it doesn't work on the client machine. Xel file is empty, even after I've generated deadlock by myself.
But on the another server, deadlocks are successfully logged, so i guess the reason in server configuration?
Could you please advise what i'm missing?
Session:
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.asynchronous_file_target
(SET filename=N'F:\PerfData\deadlocks\deadlock.xel', max_file_size=(409600),
max_rollover_files = 5)
WITH
(MAX_MEMORY=40960 KB,
EVENT_RETENTION_MODE=NO_EVENT_LOSS,
MAX_DISPATCH_LATENCY=INFINITE,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=PER_CPU,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF)
This session is active:
SELECT *
FROM sys.dm_xe_sessions
WHERE name='Deadlock_Monitor'
address name pending_buffers total_regular_buffers regular_buffer_size total_large_buffers large_buffer_size total_buffer_size
0x00000002B390C081 Deadlock_Monitor 0 20 2162483 0 0 43249660
Thank you