Updated July 5, 2016
DPA uses the SQL Server Extended Events Session to collect information about deadlocks that occurred in the database. By default, DPA creates its own Extended Events Session named
If you want to provide your own Extended Events Session to collect deadlock information, follow the instructions in this article.
DPA uses the following CREATE statement by default:
CREATE EVENT SESSION [your_session_name] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.ring_buffer(SET max_events_limit=(1000), max_memory=(2048)) WITH (MAX_MEMORY = 2048KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)
MAX_MEMORYto 2048 kB or less. Otherwise, DPA may collect deadlock information with non-deterministic delay due to truncation of the data provided by SQL Server. See this MSDN article for more information. The issue may also affect the accuracy of deadlock summary data.
max_rollover_files) under 30 MB. Large event files may cause a large amount of I/O operations on the monitored database server, especially when starting the monitor after long periods of not monitoring.
ALTER EVENT SESSION [your_session_name] ON SERVER STATE = START