How can I tell the sql server to log the whole query in the xml, and not truncate it?
example:
<inputbuf>
(@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 </inputbuf>
</process>
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
Best Answer
Every
deadlock graph
containssqlhandle
for every process that partecipates and it can be used to retrievesql_text
:where the value for parameter for
sys.dm_exec_sql_text
is thesqlhandle
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 querysys.dm_exec_sql_text
.