DBCC Table Check Long Running/Locking Issues

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » DBCC Table Check Long Running/Locking Issues

DBCC Table Check Long Running/Locking Issues

Rate Topic

Display Mode

Topic Options

Author

Message

CC-597066

Posted Tuesday, March 05, 2013 2:46 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:02 PM
Points: 46, Visits: 802

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?

Post #1427044

opc.three

Posted Tuesday, March 05, 2013 10:08 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 6,700, Visits: 11,730

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1427164

CC-597066

Posted Wednesday, March 06, 2013 3:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:02 PM
Points: 46, Visits: 802

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.

Post #1427274

opc.three

Posted Wednesday, March 06, 2013 8:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 6,700, Visits: 11,730

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1427446

CC-597066

Posted Wednesday, March 06, 2013 12:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:02 PM
Points: 46, Visits: 802

Here's hoping! Thanks for your help.

Post #1427594

« Prev Topic | Next Topic »

Permissions