CheckDB is failing

  • DBCC checkdb is failing regularly due to a dead lock. When I checked the dead lock graph, i have see it is being dead lock due to a update command.

    Can any one advise on this?

  • Post the exact command you're running and the exact error you're getting.

    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
  • Seems internally it is running the following command through a user written SP..

    DBCC UPDATEUSAGE(dbname) WITH COUNT_ROWS, NO_INFOMSGS

    Error:

    Running Command: DBCC UPDATEUSAGE(dbname) WITH COUNT_ROWS, NO_INFOMSGS [SQLSTATE 01000] (Error 0) Msg 1205, Sev 13, State 18: Transaction (Process ID 415) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 01000] (Error 0).

  • DBCC CHECKDB uses an internal database snapshot to prevents blocking. However as mention by Gail provide the detail deadlock chain/graph for further investionation.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Interesting...

    CheckDB shouldn't be trying to take locks at all. I wonder if it's failing to create the DB snapshot and resorting back to table locks. Can you check the error log, see if there's anything useful there.

    Is this Enterprise edition?

    Can you identify the stored proc that the update is part of? If so, can you post the entire proc?

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

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