dbcc checktable bombs on large tables

Question

dbcc checktable bombs on large tables

Indianrock

SSC-Insane

Points: 20333
More actions
January 28, 2016 at 9:39 am

#315447

I’m running into issues checking integrity on large tables. ( e.g. over 100 million records and greater than 200GB ). Dbcc checkdb fails in QA on a copy of prod but running checktable on each table plus checkalloc and checkcatalog should accomplish the same thing. But even here I get errors and timeouts/disconnects on the largest tables.
In the QA environment where I’ve been testing, the disk backend ( netapp ), memory and tempdb space is nearly identical to production.
DBCC CHECKTABLE ('[cmsperformance].[dbo].[XML_SERIALIZED_INSTANCE]') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
This table has 161 million rows and occupies about 500GB on disk
Sample of error log entries -- eventually this process did complete in about 90 minutes.
Date
Source
Severity
Message
1/28/2016 6:46
spid77
Unknown
External dump process return code 0x20000001.<nl/>External dump process returned no errors.
1/28/2016 6:46
spid77
Unknown
Stack Signature for the dump is 0x00000001D0362B8B
1/28/2016 6:46
spid77
Unknown
* Short Stack Dump
1/28/2016 6:46
spid77
Unknown
* -------------------------------------------------------------------------------
1/28/2016 6:46
spid77
Unknown
* *******************************************************************************
1/28/2016 6:46
spid77
Unknown
*
1/28/2016 6:46
spid77
Unknown
* nce.dbo.XML_SERIALIZED_INSTANCE'<c/> @LogToTable = 'Y'<c/> @tablock='N'
1/28/2016 6:46
spid77
Unknown
* rformance' <c/>@CheckCommands = 'CHECKTABLE' <c/>@Objects = 'cmsperforma
1/28/2016 6:46
spid77
Unknown
* EXECUTE master.dbo.DatabaseIntegrityCheck @databases = 'cmspe
1/28/2016 6:46
spid77
Unknown
* Input Buffer 430 bytes -
1/28/2016 6:46
spid77
Unknown
*
1/28/2016 6:46
spid77
Unknown
* Latch timeout
1/28/2016 6:46
spid77
Unknown
*
1/28/2016 6:46
spid77
Unknown
* 01/28/16 06:46:31 spid 6784
1/28/2016 6:46
spid77
Unknown
* BEGIN STACK DUMP:
1/28/2016 6:46
spid77
Unknown
*
1/28/2016 6:46
spid77
Unknown
* *******************************************************************************
1/28/2016 6:46
spid77
Unknown
***Stack Dump being sent to K:\data\MSSQL11.SACSQLDEVINST001\MSSQL\LOG\SQLDump0001.txt
1/28/2016 6:46
spid77
Unknown
**Dump thread - spid = 77<c/> EC = 0x00000001FBDA6160
1/28/2016 6:46
spid77
Unknown
Using 'dbghelp.dll' version '4.0.5'
1/28/2016 6:46
spid77
Unknown
Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'<c/> id 0000003DB6770488<c/> type 2<c/> Task 0x0000001497272558 : 11<c/> waittime 300 seconds<c/> flags 0x1a<c/> owning task 0x0000004A7507C928. Continuing to wait.
1/28/2016 6:46
spid77
Unknown
Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'<c/> id 0000003DB6770488<c/> type 2<c/> Task 0x00000014FFB6D498 : 15<c/> waittime 300 seconds<c/> flags 0x1a<c/> owning task 0x0000004A7507C928. Continuing to wait.
1/28/2016 6:46
spid77
Unknown
Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'<c/> id 0000003DB6770488<c/> type 2<c/> Task 0x00000014FFB5D868 : 10<c/> waittime 300 seconds<c/> flags 0x1a<c/> owning task 0x0000004A7507C928. Continuing to wait.
1/28/2016 6:46
spid77
Unknown
Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'<c/> id 0000003DB6770488<c/> type 2<c/> Task 0x000000418B478558 : 16<c/> waittime 300 seconds<c/> flags 0x1a<c/> owning task 0x0000004A7507C928. Continuing to wait.
1/28/2016 6:46
spid77
Unknown
Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'<c/> id 0000003DB6770488<c/> type 2<c/> Task 0x000000019B1150C8 : 12<c/> waittime 300 seconds<c/> flags 0x1a<c/> owning task 0x0000004A7507C928. Continuing to wait.
1/28/2016 6:46
spid77
Unknown
Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'<c/> id 0000003DB6770488<c/> type 2<c/> Task 0x00000014FFB7D868 : 13<c/> waittime 300 seconds<c/> flags 0x1a<c/> owning task 0x0000004A7507C928. Continuing to wait.
1/28/2016 6:46
spid77
Unknown
Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'<c/> id 0000003DB6770488<c/> type 2<c/> Task 0x0000004B09796558 : 14<c/> waittime 300 seconds<c/> flags 0x1a<c/> owning task 0x0000004A7507C928. Continuing to wait.
1/28/2016 6:41
spid77
Unknown
Recovery completed for database cmsperformance (database ID 71) in 156 second(s) (analysis 19 ms<c/> redo 6041 ms<c/> undo 146434 ms.) This is an informational message only. No user action is required.
1/28/2016 6:41
spid77
Unknown
1 transactions rolled back in database 'cmsperformance' (71:0). This is an informational message only. No user action is required.

Viewing 2 posts - 1 through 1 (of 1 total)

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

vitaminxu SSC Rookie Points: 29 More actions · Answer 1

Hello Indianrock, have you ever resolved dbcc error 'Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT'? If yes, mind to share? Thanks in advance!