擴展事件死鎖 xml 報告中的 SQL 查詢日誌記錄太短

如何告訴 sql server 將整個查詢記錄在 xml 中，而不是截斷它？

例子：

  <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>

這取自 sql server management studio 17.4、管理、擴展事件、會話、system_health、package0.event_file 事件詳細資訊 xml_report 值。如您所見，顯示的查詢已被截斷。有沒有辦法告訴 SQL 記錄所有查詢而不截斷它？

如果沒有辦法告訴 sql 不截斷，那麼檢索整個查詢的最佳方法是什麼？

謝謝

如果沒有辦法告訴 sql 不截斷，那麼檢索整個查詢的最佳方法是什麼

每個deadlock graph包含sqlhandle參與的每個程序，它可用於檢索sql_text：

select *
from sys.dm_exec_sql_text(0x01000c001247710230b2bfa34800000000000000000000000000000000000000000000000000000000000000)

其中參數 for 的值sys.dm_exec_sql_text是sqlhandle來自死鎖圖。

這樣，如果它的執行計劃仍在cache.

您需要VIEW SERVER STATE查詢權限sys.dm_exec_sql_text。

引用自：https://dba.stackexchange.com/questions/216768