CODEWITHSUNDEEP
sql-server
curious1
curious1

Reputation: 14727

How to use extended events to track deadlock in clustered SQL Servers

I have two clustered SQL Servers (2014 enterprise) on two virtual machines. In the logs of my web application, I saw deadlock errors. So, I did online research, found the following script, and ran it on each server:

DECLARE @targetPath nvarchar(512) = N'\MyData'; 

IF EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name = N'DeadlockMonitor')
    RETURN;

DECLARE @productVersionString nvarchar(128) = CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion'));
DECLARE @productVersion decimal(8, 5) = CAST(LEFT(@productVersionString, CHARINDEX('.', @productVersionString, 4) - 1) AS decimal(8, 5));

IF (@productVersion < 10)
BEGIN
    RAISERROR(N'Extended Events are not supported on this version of SQL Server.', 16, 1) WITH NOWAIT;
    RETURN;
END

DECLARE @fullTargetPath nvarchar(1024) =
    @targetPath +
        (CASE WHEN RIGHT(@targetPath, 1) != N'\' THEN N'\' ELSE N'' END) +
        REPLACE(@@SERVERNAME, N'\', N'$') + 
        N'.xel';


DECLARE @sql nvarchar(MAX) = N'
CREATE EVENT SESSION [DeadlockMonitor] ON SERVER 
    ADD EVENT sqlserver.xml_deadlock_report 
    ADD TARGET package0.' + (CASE WHEN @productVersion < 11 THEN N'asynchronous_file_target' ELSE N'event_file' END) + N'
    (
        SET
            FILENAME = N''' + REPLACE(@fullTargetPath, 'C:', 'E:') + ''',
            MAX_ROLLOVER_FILES = 0
    )
    WITH
    (
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 15 SECONDS,
        STARTUP_STATE = ON
    );

ALTER EVENT SESSION [DeadlockMonitor] ON SERVER
    STATE = START;'

EXEC(@sql);

A file was generated in the MyData directory after running the above script.

However, I have seen many deadlock errors in the web logs, but the file in MyData directory is still empty.

What I did is not right?

More information

I ran the above SAME code on my desktop (dev machine) with the same database, and I was able to catch deadlocks.

Upvotes: 2

Views: 93

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28930

Since you said you found this code online,i suspect

1.you might be not using path variable correctly

2.If this is your total code,it won't work since product version needs to be more refined using case statements .Since you are on 2014 i recommend to ignore those case statements ..

I was able to get deadlock from your code and below is the result with only modifications to path and setting product version to 14(2017)

enter image description here

further you can set extended events from UI as well.Also default system health log contains dead lock info for a period of time before it gets overridden

Upvotes: 2

Related Questions