Forum Discussion

little_rat's avatar
little_rat
Copper Contributor
Mar 11, 2019
Solved

how to change the inputbuf size for the deadlock.

I generate the deadlocks with long text sql, more than 2K, but when read the XDL file, I can not get the full SQL text, is there any parameter to change the SQL size for deadlock ? 
  • AMarimuthu's avatar
    AMarimuthu
    Mar 13, 2019
    It may be due to Ring buffer target
    https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/
    Solution : go with package0.asynchronous_file_target
    http://michaeljswart.com/2016/01/monitor_deadlocks/
AMarimuthu's avatar
AMarimuthu
Copper Contributor
Mar 11, 2019
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/replacing-trace-flags-1204-and-1222-with-the-extended-events-system_health-session/
little_rat's avatar
little_rat
Copper Contributor
Mar 12, 2019

AMarimuthu , Thank you for Ur update, 

BTW when I checked the system_health report , I got the same result , the deadlock graph XML can not keep all the SQL text.  just keep about 1K size text. 

also checked the SQL text with SQL handler,  in this way , I can get all the SQL text, but this is hard to make the mapping.

In the deadlock graph I got following , but there did exist some where options.

BEGIN TRANSACTION
UPDATE DlTable2 SET DL2Name = '111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

  • AMarimuthu's avatar
    AMarimuthu
    Copper Contributor
    Mar 13, 2019
    It may be due to Ring buffer target
    https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/
    Solution : go with package0.asynchronous_file_target
    http://michaeljswart.com/2016/01/monitor_deadlocks/

Resources