Deadlocks - Finding full SQL statement from deadlock report
I have an application running against a SQL Server 2016 database that is causing deadlocks.
The deadlock graph shows me the Objectid and Object name of the underlying object. It also provides with a truncated SQL statement.
However, some of the statements are quite big and exceed what appears to be a 1024 character limit in the XML deadlock report for the <inputbuf> entity. I'd like to be able to review the full statements involved in these locks so I can reproduce and debug the issue.
Is there a way this limit can be increased, or can is there a method to find the full SQL statement given the transaction descriptor which is included in the deadlock graph?
I found that I could obtain the full SQL text of each query in the deadlock using the SQL Handle string (the SQL Handle is included in the deadlock graph / XDL).
For example, within the Deadlock XML:
<frame procname="adhoc" line="1" stmtend="368" sqlhandle="0x02000000309a63065dbc0db09405222fe0f66eb954ed1d870000000000000000000000000000000000000000">
Pass sqlhandle to sys.dm_exec_sql_text
SELECT * FROM sys.dm_exec_sql_text(0x02000000309a63065dbc0db09405222fe0f66eb954ed1d870000000000000000000000000000000000000000000000000000000000000000000000000) -- modify this value with your actual sql_handle
- Using Dapper one-to-many (spliton) with generics
- How to join two SELECT queries
- Join two SELECT queries having same column names but different row values in both tables
- How can you represent inheritance in a database?
- String_agg for SQL Server before 2017
- How can I remove accents on a string?
- SQL - order by list order
- Convert varchar list to int in Sql Server
- "č" converted to "c" when querying through SQL Server 2008
- SQL Server 2016, Invalid object name 'STRING_SPLIT'
- SQL: Data Transpose
- How do I create a unique key that is case sensitive?
- ODBC Data Sources; can't add MySQL Driver
- SQL Server - how to query JSON string which has numeric value as keys
- BizTalk 2020 WCF-Sql Polling Receive Location no Response
- Pyodbc: Login Timeout Error
- Unable to upload rdl SSRS 2016
- Database's backup location that created by Azure Data Studio on Mac M1
- Entity Framework: The instance of entity type '<T>' cannot be tracked because another instance with the key value '{Id: x}' is already being tracked
- Check if a connection is in a transaction
- Can I have multiple rows of tabs in SQL Server Management Studio
- SQL Server: Make all UPPER case to Proper Case/Title Case
- How to disable transactions for individual migrations in Flyway on SQL Server
- Natural (human alpha-numeric) sort in Microsoft SQL 2005
- SQL how to do a LIKE search on each value of a declared variable
- Roll Up with column changes
- How to open a huge .sql file
- ExecuteReader TimeOut solved by changing the name of the stored procedure
- How do I get list of all tables in a database using TSQL?
- SQL Database in restoring mode