none
Deadlocks

    Question

  • Hi I have a customer that once every few months is getting a deadlock error, last time this occurred I enabled trace flag 1222 hoping it would help me trace the cause of the deadlock.

    so my question is does that trace flag only work if I have the SQL Profiler running at the time the dead lock occurs, or can I get the details after the event?


    Regards Dagz

    Monday, September 02, 2013 11:57 AM

Answers

All replies

  • Try to check the SQL Server error log now. 

    It's enough to enable the trace flag 1222 to get the detailed information logged to log file. 

    http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/

    • Marked as answer by Dagz Monday, September 02, 2013 12:29 PM
    Monday, September 02, 2013 12:02 PM
  • Hi thanks I was hoping this was the case but the error lock does not seem to contain and deadlock information

    the startup parameter says -dE:\Microsoft SQL Server\SQL2008\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eE:\Microsoft SQL Server\SQL2008\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lE:\Microsoft SQL Server\SQL2008\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf; -t1222

    but there is nothing in the log?


    Regards Dagz

    Monday, September 02, 2013 12:15 PM
  • I think the trace flag is wrongly set. It is set to  -t1222  while it should be  -T1222

    Also, bear in mind that there are various options to troubleshoot deadlocks:

    1) Enable Trace Flags 1204 and 1222:

    Detecting and Ending Deadlocks

    http://msdn.microsoft.com/en-us/library/ms178104(v=SQL.90).aspx

    After the deadlock reoccurs, the deadlock information will be captured in  the

    2) Capture a Profiler trace that also captures the Deadlock Graph event. After the deadlock occurs, stop the trace and check the captured deadlock graph event:

     Analyzing Deadlocks with SQL Server Profiler

    http://msdn.microsoft.com/en-us/library/ms188246(v=SQL.90).aspx

    Regards,

    John Daskalakis


    JODASK

    • Marked as answer by Dagz Monday, September 02, 2013 12:29 PM
    Monday, September 02, 2013 12:22 PM
  • how annoying, ok thank for the advice I will make the changes and hopefully next time it occurs I will get the information I need to trace the problem.

    Regards Dagz

    Monday, September 02, 2013 12:29 PM