+ Reply to Thread
Results 1 to 6 of 6

Help in deadlock error log

  1. Help in deadlock error log

    Hi,

    We are running SQL Server 2005 and we've set the trace to 1222. It
    encountered a deadlock and below is an excerpt of the deadlock.

    3/2/2009 14:09 spid11s Unknown Deadlock encountered .... Printing deadlock
    information
    3/2/2009 14:09 spid11s Unknown 1:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 3:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown search result: Deadlock found
    (blocking owner is on a stack)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown Cycle found between old res
    owner: [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380 Cost0/936)] and
    new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380 Cost0/936)]
    3/2/2009 14:09 spid11s Unknown 3:SearchOR Considering new blocker - task:
    00BE25C8 Worker 9098E0E8
    3/2/2009 14:09 spid11s Unknown 2:Insert new node: Node:2
    3/2/2009 14:09 spid11s Unknown 1:SearchOR Considering new blocker - task:
    0581D108 Worker 6087E0E8
    3/2/2009 14:09 spid11s Unknown 0:Insert new node: Node:1
    3/2/2009 14:09 spid11s Unknown Deadlock cycle was encountered .... verifying
    cycle
    3/2/2009 14:09 spid11s Unknown Log Viewer could not read information for
    this log entry. Cause: Data is Null. This method or property cannot be called
    on Null values.. Content:
    3/2/2009 14:09 spid11s Unknown 1:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 3:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown search result: Deadlock found
    (blocking owner is on a stack)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown Cycle found between old res
    owner: [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380] and new res owner [
    ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280 BatchID:0 ECID:0
    TaskProxy0x32D14378) Value:0x2a2c7380]
    3/2/2009 14:09 spid11s Unknown 3:SearchOR Considering new blocker - task:
    00BE25C8 Worker 9098E0E8
    3/2/2009 14:09 spid11s Unknown 2:Insert new node: Node:2
    3/2/2009 14:09 spid11s Unknown 1:SearchOR Considering new blocker - task:
    0581D108 Worker 6087E0E8
    3/2/2009 14:09 spid11s Unknown 0:Insert new node: Node:1
    3/2/2009 14:09 spid11s Unknown ResType:LockOwner Stype:'OR'Xdes:0x30E92250
    Mode: U SPID:280 BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380


    I can't seem to find what the actual statement was execute to cause the
    deadlock. Does anyone know what the above information is saying with regards
    to the deadlock it encountered?

    Thanks in advance,
    Dee

  2. Re: Help in deadlock error log

    If you are on SQL Server 2005/2008, then use Trace flag 1222 to get the
    deadlock graph and it will include the executing statements as well. If you
    are on 2000, then you will need to combine the TF 1204 output with a
    profiler trace that includes the starting events for RPC/SQLBatch/SP's and
    the Deadlock Chain event. This will show allow you to get the statements
    from the text data of the spid's listed in the deadlock chain.

    --
    Jonathan Kehayias
    SQL Server MVP
    http://jmkehayias.blogspot.com
    http://www.sqlclr.net


    "bpdee" wrote in message
    news:A477F071-C2F7-49A4-9E91-243A12D4FE11atmicrosoftdotcom...
    > Hi,
    >
    > We are running SQL Server 2005 and we've set the trace to 1222. It
    > encountered a deadlock and below is an excerpt of the deadlock.
    >
    > 3/2/2009 14:09 spid11s Unknown Deadlock encountered .... Printing deadlock
    > information
    > 3/2/2009 14:09 spid11s Unknown 1:SearchOR search result: Deadlock found
    > (cycle on this level or before)
    > 3/2/2009 14:09 spid11s Unknown 3:SearchOR search result: Deadlock found
    > (cycle on this level or before)
    > 3/2/2009 14:09 spid11s Unknown 4:InsertKnown search result: Deadlock found
    > (blocking owner is on a stack)
    > 3/2/2009 14:09 spid11s Unknown 4:InsertKnown Cycle found between old res
    > owner: [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    > BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380 Cost0/936)] and
    > new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U
    > SPID:280
    > BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380 Cost0/936)]
    > 3/2/2009 14:09 spid11s Unknown 3:SearchOR Considering new blocker - task:
    > 00BE25C8 Worker 9098E0E8
    > 3/2/2009 14:09 spid11s Unknown 2:Insert new node: Node:2
    > 3/2/2009 14:09 spid11s Unknown 1:SearchOR Considering new blocker - task:
    > 0581D108 Worker 6087E0E8
    > 3/2/2009 14:09 spid11s Unknown 0:Insert new node: Node:1
    > 3/2/2009 14:09 spid11s Unknown Deadlock cycle was encountered ....
    > verifying
    > cycle
    > 3/2/2009 14:09 spid11s Unknown Log Viewer could not read information for
    > this log entry. Cause: Data is Null. This method or property cannot be
    > called
    > on Null values.. Content:
    > 3/2/2009 14:09 spid11s Unknown 1:SearchOR search result: Deadlock found
    > (cycle on this level or before)
    > 3/2/2009 14:09 spid11s Unknown 3:SearchOR search result: Deadlock found
    > (cycle on this level or before)
    > 3/2/2009 14:09 spid11s Unknown 4:InsertKnown search result: Deadlock found
    > (blocking owner is on a stack)
    > 3/2/2009 14:09 spid11s Unknown 4:InsertKnown Cycle found between old res
    > owner: [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    > BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380] and new res
    > owner [
    > ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280 BatchID:0
    > ECID:0
    > TaskProxy0x32D14378) Value:0x2a2c7380]
    > 3/2/2009 14:09 spid11s Unknown 3:SearchOR Considering new blocker - task:
    > 00BE25C8 Worker 9098E0E8
    > 3/2/2009 14:09 spid11s Unknown 2:Insert new node: Node:2
    > 3/2/2009 14:09 spid11s Unknown 1:SearchOR Considering new blocker - task:
    > 0581D108 Worker 6087E0E8
    > 3/2/2009 14:09 spid11s Unknown 0:Insert new node: Node:1
    > 3/2/2009 14:09 spid11s Unknown ResType:LockOwner Stype:'OR'Xdes:0x30E92250
    > Mode: U SPID:280 BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380
    >
    >
    > I can't seem to find what the actual statement was execute to cause the
    > deadlock. Does anyone know what the above information is saying with
    > regards
    > to the deadlock it encountered?
    >
    > Thanks in advance,
    > Dee




  3. Help in deadlock error log

    John:

    Did you find out what happened? How did you solve it? I have the same issue. Thanks

    Sherry



    Jonathan Kehayias wrote:

    Re: Help in deadlock error log
    04-Feb-09

    If you are on SQL Server 2005/2008, then use Trace flag 1222 to get the
    deadlock graph and it will include the executing statements as well. If you
    are on 2000, then you will need to combine the TF 1204 output with a
    profiler trace that includes the starting events for RPC/SQLBatch/SP's and
    the Deadlock Chain event. This will show allow you to get the statements
    from the text data of the spid's listed in the deadlock chain.

    --
    Jonathan Kehayias
    SQL Server MVP
    http://jmkehayias.blogspot.com
    http://www.sqlclr.net


    "bpdee" wrote in message
    news:A477F071-C2F7-49A4-9E91-243A12D4FE11atmicrosoftdotcom...

    EggHeadCafe - Software Developer Portal of Choice
    WPF DataGrid Custom Paging and Sorting
    http://www.eggheadcafe.com/tutorials...tom-pagin.aspx

  4. Help in Deadlock error

    Dee:

    Did you find out what happened? How did you solve it? I have the same issue. Thanks

    Sherry



    bpde wrote:

    Help in deadlock error log
    03-Feb-09

    Hi,

    We are running SQL Server 2005 and we've set the trace to 1222. It
    encountered a deadlock and below is an excerpt of the deadlock.

    3/2/2009 14:09 spid11s Unknown Deadlock encountered .... Printing deadlock
    information
    3/2/2009 14:09 spid11s Unknown 1:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 3:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown search result: Deadlock found
    (blocking owner is on a stack)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown Cycle found between old res
    owner: [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380 Cost0/936)] and
    new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380 Cost0/936)]
    3/2/2009 14:09 spid11s Unknown 3:SearchOR Considering new blocker - task:
    00BE25C8 Worker 9098E0E8
    3/2/2009 14:09 spid11s Unknown 2:Insert new node: Node:2
    3/2/2009 14:09 spid11s Unknown 1:SearchOR Considering new blocker - task:
    0581D108 Worker 6087E0E8
    3/2/2009 14:09 spid11s Unknown 0:Insert new node: Node:1
    3/2/2009 14:09 spid11s Unknown Deadlock cycle was encountered .... verifying
    cycle
    3/2/2009 14:09 spid11s Unknown Log Viewer could not read information for
    this log entry. Cause: Data is Null. This method or property cannot be called
    on Null values.. Content:
    3/2/2009 14:09 spid11s Unknown 1:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 3:SearchOR search result: Deadlock found
    (cycle on this level or before)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown search result: Deadlock found
    (blocking owner is on a stack)
    3/2/2009 14:09 spid11s Unknown 4:InsertKnown Cycle found between old res
    owner: [ ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280
    BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380] and new res owner [
    ResType:LockOwner Stype:'OR'Xdes:0x30E92250 Mode: U SPID:280 BatchID:0 ECID:0
    TaskProxy0x32D14378) Value:0x2a2c7380]
    3/2/2009 14:09 spid11s Unknown 3:SearchOR Considering new blocker - task:
    00BE25C8 Worker 9098E0E8
    3/2/2009 14:09 spid11s Unknown 2:Insert new node: Node:2
    3/2/2009 14:09 spid11s Unknown 1:SearchOR Considering new blocker - task:
    0581D108 Worker 6087E0E8
    3/2/2009 14:09 spid11s Unknown 0:Insert new node: Node:1
    3/2/2009 14:09 spid11s Unknown ResType:LockOwner Stype:'OR'Xdes:0x30E92250
    Mode: U SPID:280 BatchID:0 ECID:0 TaskProxy0x32D14378) Value:0x2a2c7380


    I can't seem to find what the actual statement was execute to cause the
    deadlock. Does anyone know what the above information is saying with regards
    to the deadlock it encountered?

    Thanks in advance,
    Dee

    EggHeadCafe - Software Developer Portal of Choice
    UrlKicker-Url / Search / Notes Tray Utility
    http://www.eggheadcafe.com/tutorials...--search-.aspx

  5. Re: Help in Deadlock error

    (Sherry Lee) writes:
    > Did you find out what happened? How did you solve it? I have the same
    > issue. Thanks


    What is same issue? There are so many ways a deadlock can occur, so what
    was the resolution for one guy may be entirely irrelevant for the next.

    Troubleshooting over newsgroups is not entirely easy, but you could
    start with posting your deadlock trace.

    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  6. Re: Help in Deadlock error

    I agree with Erland deadlock can occur many different ways. You need to
    post the trace files ...
    Lito Dominguez

    "Erland Sommarskog" wrote:

    > (Sherry Lee) writes:
    > > Did you find out what happened? How did you solve it? I have the same
    > > issue. Thanks

    >
    > What is same issue? There are so many ways a deadlock can occur, so what
    > was the resolution for one guy may be entirely irrelevant for the next.
    >
    > Troubleshooting over newsgroups is not entirely easy, but you could
    > start with posting your deadlock trace.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >
    >


+ Reply to Thread