trace is not capturing DeadLock

  • HI,

    I found deadlock on one of my db server. at first I tried to capture it with profiler.I had added

    Deadlock, Deadlock chain and deadlock graph, sql stmt start,sqlstmt completed as selection of events. Profiler only captured deadlock chain "Parallel query causing deadlock", but no graph or statement that is victim of deadlock.

    So I enabled server trace with following sql

    DBCC TRACEON(-1, 1204, 1222)

    I have no deadlock event under SqlServer- Error Logs in SSMS.

    Am i looking in correct place to see logs? or do I need to add some additional flags in trace ?

    Thanks

  • Could be the deadlock hasn't reoccured.

    You don'yt need 1204, that the SQL 2000 traceflag for capturing deadlocks, and to be honest you don't need 1222 in SQL 2012, use the system health extended event session, which captures deadlocks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you Gail for updated information.

    I am studying these event and will get back if any other issue arises.

    But just for knowledge, what could be the reason with the approach I followed ?

  • thbaig - Tuesday, August 23, 2016 10:56 AM

    Thank you Gail for updated information.I am studying these event and will get back if any other issue arises.But just for knowledge, what could be the reason with the approach I followed ?

    Even am facing the same issue as explained here.  I have enabled trace flags 1222 and 1204 globally on a DB server (SQL 2012) to capture deadlock details. When I simulated the deadlock by running the queries it captured the details. But when the deadlock is happening thru' web service calls it is not capturing the error details at all. What could be wrong? We are seeing deadlock error for other DBs in the same errorlog file.

  • rajeshsbhat - Thursday, January 19, 2017 4:06 AM

    thbaig - Tuesday, August 23, 2016 10:56 AM

    Thank you Gail for updated information.I am studying these event and will get back if any other issue arises.But just for knowledge, what could be the reason with the approach I followed ?

    Even am facing the same issue as explained here.  I have enabled trace flags 1222 and 1204 globally on a DB server (SQL 2012) to capture deadlock details. When I simulated the deadlock by running the queries it captured the details. But when the deadlock is happening thru' web service calls it is not capturing the error details at all. What could be wrong? We are seeing deadlock error for other DBs in the same errorlog file.

    Check the system health extended event session as Gail has suggested. Here's an article on it. Extended events, especially in SQL Server 2012 and greater, are much more efficient than trace. They're likely to be more accurate too.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • thbaig - Tuesday, August 23, 2016 10:24 AM

    HI,I found deadlock on one of my db server. at first I tried to capture it with profiler.I had addedDeadlock, Deadlock chain and deadlock graph, sql stmt start,sqlstmt completed as selection of events. Profiler only captured deadlock chain "Parallel query causing deadlock", but no graph or statement that is victim of deadlock.So I enabled server trace with following sql DBCC TRACEON(-1, 1204, 1222)I have no deadlock event under SqlServer- Error Logs in SSMS.Am i looking in correct place to see logs? or do I need to add some additional flags in trace ?Thanks

    Your trace command above is incorrect.  The "-1" needs to be the last operand.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply