Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC Table Check Long Running/Locking Issues Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 2:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:32 PM
Points: 51, Visits: 898
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
Posted Tuesday, March 5, 2013 10:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
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
Post #1427164
Posted Wednesday, March 6, 2013 3:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:32 PM
Points: 51, Visits: 898
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
Posted Wednesday, March 6, 2013 8:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
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
Post #1427446
Posted Wednesday, March 6, 2013 12:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:32 PM
Points: 51, Visits: 898
Here's hoping! Thanks for your help.
Post #1427594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse