Post reply

Can I kill any one of deadlocked process?

  • July 28, 2011 at 5:55 am

    #255004

    Can I kill any one of deadlocked process?

    Thanks

  • July 28, 2011 at 5:56 am

    #1359970

    In a deadlock, SQL's deadlock detector will pick one of the participants and roll it back automatically.

    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 28, 2011 at 5:59 am

    #1359975

    As Gail said, SQL Server makes the choice in the deadlock situation. You don't get to pick. The only way you could pick is to do it manually, but usually you'll get there too late (during rollback), so killing the process wouldn't do you any good because SQL would still continue the rollback.

    And you should let it rollback. Don't try to outsmart SQL on this because you might cause problems if you do.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 28, 2011 at 7:34 am

    #1360083

    forsqlserver (7/28/2011)

    Can I kill any one of deadlocked process?

    Killing a process sometimes is a sin. 4 Years back when I was just into SQL dba work, I found that a developer executed a procedure in DEV env. but it was still inserting into a remote production DB(linked server ) and the insert was for 30+ million rows. I killed it and shot a mail to everyone that things are fine and in a way announced that I am a DBA now.

    I regretted later and found that rollback took as much as time as it was in the beginning.

    So analyze the advice from experts here.

  • July 28, 2011 at 10:48 pm

    #1360542

    forsqlserver (7/28/2011)

    Can I kill any one of deadlocked process?

    You cant kill. But you can SET DEADLOCK_PRIORITY.

  • July 29, 2011 at 12:14 am

    #1360554

    Suresh can u explore how to set deadlock_prority

    Thanks

  • July 29, 2011 at 1:05 am

    #1360564

    forsqlserver (7/29/2011)

    Suresh can u explore how to set deadlock_prority

    Sorry, I don't have any additional information than what is mentioned in BOL.

    Hope others will share their experience with this.

  • July 29, 2011 at 1:22 am

    #1360569

    forsqlserver (7/29/2011)

    Suresh can u explore how to set deadlock_prority

    Did you look in Books Online? It is documented quite well.

    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 8 posts - 1 through 7 (of 7 total)

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