Post reply

deadlock

  • July 16, 2012 at 7:57 pm

    #259562

    Hi,

    how to find out the which transaction is killed in deadlock.

    I got below alert

    Transaction (Process ID 150) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

    I need to know the two proceses and who ran those two transaction and what sql script they are running...

    Is there any way to check in sql?

  • July 16, 2012 at 10:38 pm

    #1513379

    FIRST USE:SP_WHO2 ACTIVE COMMAND FILE IN MASTER DATABASE:

    THAT TIME YOU WILL GET SOME OUT PUT.

    pls check bikby column .

    if any id was showing that column.

    pls use that id below mentation command.

    use below mentation command.

    dbcc inputbuffer (spid)

  • July 16, 2012 at 10:39 pm

    #1513380

    dbcc traceon (1204, 3605, -1)

    go

    dbcc tracestatus(-1)

    go

    The -T1204 startup parameter collects information about the process and the resources when the deadlock detection algorithm encounters a deadlock. The -T3605 startup parameter writes this information to the SQL Server error logs. The "-1" indicates all SPIDs.

  • July 17, 2012 at 1:24 am

    #1513430

    best deadlocking article I have ever read is from the MVP deep dives book - written by Gail shaw, helped me loads. Worth buying the book just for that article.

  • July 17, 2012 at 1:30 am

    #1513432

    Personally I would enable trace flag 1222 over 1204 as it captures more detailed information with the deadlock graph which helps to troubleshoot the commands in the deadlock. Makes it much easier to troubleshoot.

  • July 17, 2012 at 3:33 am

    #1513466

    Don't use 1204 on SQL 2008, it's the old traceflag and it produces very hard to read info. 3605 is not necessary for deadlock at all.

    In SQL 2008, you can just check the system_health extended events session and pull the deadlock graph out of that. Otherwise wwitch traceflag 1222 on.

    DBCC TRACEON(1222,-1)

    sp_who, DBCC Inputbuffer, sys.dm_exec_requests, etc are all useless after the deadlock as by that point SQL has already killed and rolled back the victim

    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

  • July 17, 2012 at 3:40 am

    #1513468

    I agree with Gail.

    However a small doubt, System_health might also not have those details if the deadlock happened for quite long time. It might be overwritten. Please correct me if am wrong.

  • July 17, 2012 at 3:44 am

    #1513473

    sqlzealot-81 (7/17/2012)

    However a small doubt, System_health might also not have those details if the deadlock happened for quite long time. It might be overwritten. Please correct me if am wrong.

    Correct, but errorlogs get cycled too and system health is the only way to look into the past if the traceflag isn't already enabled.

    It's possible to archive necessary events from system health (just like with the default trace)

    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

  • July 17, 2012 at 3:50 am

    #1513476

    GilaMonster (7/17/2012)

    sqlzealot-81 (7/17/2012)

    However a small doubt, System_health might also not have those details if the deadlock happened for quite long time. It might be overwritten. Please correct me if am wrong.

    Correct, but errorlogs get cycled too and system health is the only way to look into the past if the traceflag isn't already enabled.

    Yes absolutely.

    It's possible to archive necessary events from system health (just like with the default trace)

    This is really interesting. Could you please share some script to do so or any link handy?

  • July 17, 2012 at 3:57 am

    #1513482

    Google? (Jonathan Kehayias's blog is a good place to start)

    Any by archive, I mean query the system health and insert any events you want to keep into an archive table.

    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

  • July 17, 2012 at 4:38 am

    #1513504

    GilaMonster (7/17/2012)

    Google? (Jonathan Kehayias's blog is a good place to start)

    Any by archive, I mean query the system health and insert any events you want to keep into an archive table.

    Ok, Clear now. Thank you for the clarification.

Viewing 11 posts - 1 through 10 (of 10 total)

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