SQL Extended Events Session for deadlock detection

Is there a way to increase the size of the ` element in the deadlock XML captured by the deadlock extended events session?

We want to see the complete query to help pinpoint the issue in the Application code.

It seems to be limited to 1024 characters +/-. Can it be increased?

See below for sample XML. You can see that the query text in the ` element is cut off in the middle of the select list:

 
  
 
 
  
   
    
unknown    
    
unknown    
   
   
(@p__linq__0 int,@p__linq__1 int)SELECT 
[Project1].[CachedBuildStateId] AS [CachedBuildStateId], 
[Project1].[BuildVersionId1] AS [BuildVersionID], 
[Project1].[ProjectID] AS [ProjectID], 
[Project1].[VersionName] AS [VersionName], 
[Project1].[PlatformID] AS [PlatformID], 
[Project1].[VersionPath] AS [VersionPath], 
[Project1].[RegionID] AS [RegionID], 
[Project1].[TestStatusID] AS [TestStatusID], 
[Project1].[TestResult] AS [TestResult], 
[Project1].[TestReportPath] AS [TestReportPath], 
[Project1].[TypeID] AS [TypeID], 
[Project1].[Size] AS [Size], 
[Project1].[VersionDate] AS [VersionDate], 
[Project1].[Approved] AS [Approved], 
[Project1].[Archived] AS [Archived], 
[Project1].[CLData] AS [CLData], 
[Project1].[CLCode] AS [CLCode], 
[Project1].[CLSound] AS [CLSound], 
[Project1].[XDKSDKVersion] AS [XDKSDKVersion], 
[Project1].[Comments] AS [Comments], 
[Project1].[DateAdded] AS [DateAdded], 
[Project1].[DateModified] AS [DateModified], 
[Project1].[CacheLocally] AS [CacheLocally],   
  
  
   
    
unknown    
    
unknown    
   
   
(@0 int,@1 datetime,@2 datetime,@3 int)update [dbo].[CachedBuildState]
set [CachingStatusId] = @0, [StartTimeUtc] = @1, [LastUpdateTimeUtc] = @2, [MessageDetails] = null
where ([CachedBuildStateId] = @3)
   
  
 
 
  
   
    
   
   
    
   
  
  
   
    
   
   
    
   
  
 

You have a sqlhandle for each frame so you are able to get the complete query using sys.dm_exec_sql_text function:

SELECT text
FROM sys.dm_exec_sql_text(sql_handle)

sys.dm_exec_sql_text

For instance, getting text of some statements from sys.dm_exec_query_stats (stmtstart, stmtend, sqlhandle instead of statement_start_offset, statement_end_offset, sql_handle in the XML report)

SELECT TOP (100)
    CASE   
        WHEN s.[statement_start_offset] > 0 THEN  
            --The start of the active command is not at the beginning of the full command text 
            CASE s.[statement_end_offset]  
               WHEN -1 THEN  
                  --The end of the full command is also the end of the active statement 
                  SUBSTRING(t.TEXT, (s.[statement_start_offset]/2) + 1, 2147483647) 
               ELSE   
                  --The end of the active statement is not at the end of the full command 
                  SUBSTRING(t.TEXT, (s.[statement_start_offset]/2) + 1, (s.[statement_end_offset] - s.[statement_start_offset])/2+1)   
            END  
        ELSE  
            --1st part of full command is running 
            CASE s.[statement_end_offset]  
               WHEN -1 THEN  
                  --The end of the full command is also the end of the active statement 
                  RTRIM(LTRIM(t.[text]))  
               ELSE  
                  --The end of the active statement is not at the end of the full command 
                  LEFT(t.TEXT, (s.[statement_end_offset]/2) +1)  
            END  
        END AS [executing statement]
FROM sys.dm_exec_query_stats s 
    CROSS APPLY sys.dm_exec_sql_text(s.[sql_handle]) t

UPD

Thanks to @JohnEisbrener's reasonable comment I should notice that information from DMVs will be actual until the query cache is cleaned. So if you expect to use that information after cache cleaning (for example after your server restart), you have to save that data in some permanent table immediately after the event occured.

StackExchange Database Administrators Q#237130, answer score: 2