Post reply

DBCC Table Check Long Running/Locking Issues

  • March 5, 2013 at 2:46 pm

    #274832

    I have two 'identical' systems, both running SQL 2008 Enterprise with SP2 and Windows 2008 64bit. The DB's are around 800GB with 31,000 tables. When DBCC CHECKDB gets to the TABLE CHECK stage, on one it takes out a Sch-S lock on the table it is doing then clears the lock and moves on to the next table.

    When it runs that on the other it also takes out a Sch-S lock but doesn't release the previous one before it takes out the next one so the number of locks grows and grows as the DBCC progresses. The DBCC also runs for at least four times longer than normal when this behaviour happens. The estimated_completion_time in sys.dm_exec_requests seems to be correct to start with but extends as the job runs longer and longer. A reboot seems to fix the issue and it has happened 4 times in 12 months.

    The job isn't explicitly set to use TABLOCK and there are no messages in the errorlog. None of the reasons listed in the help page for DBCC regarding reasons why a snapshot can't be made seem applicable but do I assume it isn't using one??

    Any thoughts on what could cause this or what to look for next time it happens?

  • March 5, 2013 at 10:08 pm

    #1593758

    What is the 'max degree of parallelism' setting on both systems and is the one that is having the trouble under heavy load in terms of how many modifications it is accepting while CHECKDB is running?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 6, 2013 at 3:48 am

    #1593848

    Thanks for your reply. max degree of parallelism is set to 1 on both servers. They are SAP systems and that is their recommended setting for it.

    Load is generally light as the DBCC is timed to run over the weekend when nothing else is happening.

    DBCC can run fine for months but once it starts playing up it won't run properly again until the server is rebooted which seems to point to some sort of resource issue.

  • March 6, 2013 at 8:17 am

    #1594000

    Sounds odd. Maybe you'll get lucky and Gail or Paul will drop by and relay something they have heard or seen about the issue you are seeing.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 6, 2013 at 12:37 pm

    #1594130

    Here's hoping! Thanks for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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