Post reply

How to monitor and resolve deadlock in SQL 2005 ?

  • November 17, 2010 at 7:17 pm

    #143553

    Hi all,

    I have monitor SQL service and sometime I has received email system warring deadlock. But I don't know deadlock at object.

    Please help me to resolve it

    thanks,

    phuongnh

  • November 17, 2010 at 9:36 pm

    #1252264

    Lookup "Troubleshooting Deadlocks" in Books Online. They have a better writeup there than I could give you here.

    --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)

  • November 17, 2010 at 10:45 pm

    #1252282

    Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    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

  • November 17, 2010 at 11:30 pm

    #1252289

    Thank for response,

    But I don't know what is it deadlock on objects, therefore can't resolve it

    Can you help full ?

    phuongnh

  • November 18, 2010 at 1:02 am

    #1252298

    phuong.nguyen-1008376 (11/17/2010)

    Thank for response,

    But I don't know what is it deadlock on objects, therefore can't resolve it

    Do you mean to say you do not understand deadlocks or do you mean you cannot find out deadlocks are happening on which objects?

    For first one, pls read what is suggested by Jeff.

    For second one, pls enable trace flag 1222 as suggested by Gail. By doing this, whenever deadlock occurs, the deadlock information will be printed in the sql server error log including resources and queries participating in the deadlock. you can find information from there.


    Pradeep Singh

  • November 18, 2010 at 1:04 am

    #1252299

    Are you saying you don't know what a deadlock is or that you don't know what objects it is on?

    If you don't know what a deadlock is, do some reading, books online or a good google search. I can recommend a book that has a chapter on deadlocks and reading the deadlock graph if you like.

    If you don't know what objects the deadlock is on, see my previous comment. That traceflag will produce a deadlock graph that will tell you what objects the deadlock involves.

    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

  • November 18, 2010 at 8:04 pm

    #1252829

    phuong.nguyen-1008376 (11/17/2010)

    Thank for response,

    But I don't know what is it deadlock on objects, therefore can't resolve it

    Can you help full ?

    phuongnh

    The best way for me to be helpful in this case is to tell you exactly what I told you. Lookup troublshooting deadlocks in Books Online (the free help system that comes with SQL Server) where it will tell you what a deadlock is, how to detect them, how to fix them, and how to avoid them in the future. There's no simple way to learn about them. Do the reading. 😉

    --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)

  • November 19, 2010 at 8:54 am

    #1253112

    You can use SQL Server Profiler -> Deadlock graph Event. It will give you information about objects and statments.

  • November 19, 2010 at 10:03 am

    #1253182

    For me, in order to "fully understand" deadlock issues, you need to be able to reproduce one, see the output it generates, and resolve it.

    Check out this article,

    http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx?PageIndex=1

    It gives step-by-steps instructions on what a deadlock is, how to create one, where to look for on the log files, and how to resolve it.

    Good luck.

  • November 30, 2010 at 9:21 pm

    #1257119

    Thanks for support. I resolved it

  • November 30, 2010 at 9:43 pm

    #1257124

    phuong.nguyen-1008376 (11/30/2010)

    Thanks for support. I resolved it

    Now that you have it resolved, do you better understand what a deadlock is or how to find it?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 2, 2010 at 12:03 am

    #1257734

    Hi all,

    I had monitor deadlock

    ]http://picasaweb.google.com/109654709269383255040/Forum?authkey=Gv1sRgCO-I-5PzqNKERQ#]

    How we can resolve the deadlock?

    thanks,

    phuongnh

  • December 2, 2010 at 2:04 am

    #1257749

    Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    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

Viewing 13 posts - 1 through 12 (of 12 total)

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