SQL Query logging in extended events deadlock xml report is too short

How can I tell the sql server to log the whole query in the xml, and not truncate it?

example:

(@0 nvarchar(20),@1 nvarchar(10),@2 decimal(38,20),@3 int,@4 datetime,@5 nvarchar(10))IF EXISTS(SELECT TOP 1 NULL FROM "MySQLDB".dbo."MyTable$Reservation Entry" WITH(UPDLOCK)  WHERE ("Item No_"=@0 AND "Location Code"=@1 AND "Quantity (Base)">@2 AND "Reservation Status"=@3 AND "Expected Receipt Date"<=@4 AND "Variant Code"=@5)) SELECT "timestamp","Entry No_","Positive","Item No_","Location Code","Quantity (Base)","Reservation Status","Description","Creation Date","Transferred from Entry No_","Source Type","Source Subtype","Source ID","Source Batch Name","Source Prod_ Order Line","Source Ref_ No_","Item Ledger Entry No_","Expected Receipt Date","Shipment Date","Serial No_","Created By","Changed By","Qty_ per Unit of Measure","Quantity","Binding","Suppressed Action Msg_","Planning Flexibility","Appl_-to Item Entry","Warranty Date","Expiration Date","Qty_ to Handle (Base)","Qty_ to Invoice (Base)","Quantity Invoiced (Base)","New Serial No_","New Lot No_","Disallow Cancellation","Lot No_","Vari   
  

this is taken from sql server management studio 17.4, management, extended events, sessions, system_health, package0.event_file event detail xml_report value. As you can see the query shown is truncated. Is there a way to tell SQL to log all of the query without truncating it?

If there isn't a way to tell sql not truncate, what is the best way of retrieving the whole query?

Thank you

If there isn't a way to tell sql not truncate, what is the best way of
retrieving the whole query

Every deadlock graph contains sqlhandle for every process that partecipates and it can be used to retrieve sql_text:

select *
from sys.dm_exec_sql_text(0x01000c001247710230b2bfa34800000000000000000000000000000000000000000000000000000000000000)

where the value for parameter for sys.dm_exec_sql_text is the sqlhandle from deadlock graph.

This way you may get your statement if its execution plan is still in cache.

You need the VIEW SERVER STATE permission to query sys.dm_exec_sql_text.

StackExchange Database Administrators Q#216768, answer score: 5