SQL Server Performance Forum – Threads Archive
DBCC CHECKDB HELP
HI DBAs, i just want to get your view with DBCC CHECKDB… I am running DBCC CHECKDB WITH FIX OPTION on our DEV Server since monday…and its now about 52 hours and still going. the size of the database is 168gb…based on your experience, does it really takes this long to process this? Can it be measured in terms of hours against 168gb of Database? Is there any other way to fix the integrity error / corruption error in the DB? Help Please1. first check space in your tempdb with
dbcc checkdb (databasename) WITH ESTIMATEONLY.
2. run CHECKDB when the system usage is low.
3. what are the disk queue lengths on the drives holding the database? (i.e
is your IO subsystem the bottleneck).
4. does it complete a lot faster if you use the NOINDEX (see BOL) option?. You may have corruption somewhere in a non-clustered index
which is triggering a much more expensive set of checks to find the exact
row with the corruption in. This being the case you can remove the NOINDEX option to let
it complete and then you should be able to see where the corruption exists. Dbu
All postings are provided “AS IS†with no warranties for accuracy.
Hi Dbu,
It is actually running right now since last monday and no one is using the server since then. I just look the perf monitor the IO dont go to from 95%. When I run it with DBCC CHECKDB(‘DB’,NOINDEX) it ran for less than 1hr and there are some allocation error on the database, so I decided to run the statement with REPAIR_REBUILD and now it is still running.
quote:Originally posted by dbu
1. first check space in your tempdb with
dbcc checkdb (databasename) WITH ESTIMATEONLY.
2. run CHECKDB when the system usage is low.
3. what are the disk queue lengths on the drives holding the database? (i.e
is your IO subsystem the bottleneck).
4. does it complete a lot faster if you use the NOINDEX (see BOL) option?. You may have corruption somewhere in a non-clustered index
which is triggering a much more expensive set of checks to find the exact
row with the corruption in. This being the case you can remove the NOINDEX option to let
it complete and then you should be able to see where the corruption exists. Dbu
All postings are provided “AS IS†with no warranties for accuracy.
dbcc checkdb (databasename) WITH ESTIMATEONLY.
2. run CHECKDB when the system usage is low.
3. what are the disk queue lengths on the drives holding the database? (i.e
is your IO subsystem the bottleneck).
4. does it complete a lot faster if you use the NOINDEX (see BOL) option?. You may have corruption somewhere in a non-clustered index
which is triggering a much more expensive set of checks to find the exact
row with the corruption in. This being the case you can remove the NOINDEX option to let
it complete and then you should be able to see where the corruption exists. Dbu
All postings are provided “AS IS†with no warranties for accuracy.
Is good choise REPAIR_REBUILD.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Hi DBAs,<br /><br />My DBCC CHECKDB finished for about 93 hours…(ouchhhhh…)<br />and it gave me this kind of report…any idea how to fix this<br /><br />DBCC results for ‘tblCameoUKGroupDescriptions’.<br />Server: Msg 8928, Level 16, State 1, Line 1<br />Object ID 394484484, index ID 5: Page (1:881467) could not be processed. See other errors for details.<br />Server: Msg 8976, Level 16, State 1, Line 1<br />Table error: Object ID 394484484, index ID 5. Page (1:881467) was not seen in the scan although its parent (1:881369) and previous (1:881466) refer to it. Check any previous errors.<br />Server: Msg 8978, Level 16, State 1, Line 1<br />Table error: Object ID 394484484, index ID 5. Page (1:88146<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> is missing a reference from previous page (1:881467). Possible chain linkage problem.<br />Server: Msg 8928, Level 16, State 1, Line 1<br />Object ID 394484484, index ID 6: Page (1:547756) could not be processed. See other errors for details.<br />Server: Msg 8976, Level 16, State 1, Line 1<br />Table error: Object ID 394484484, index ID 6. Page (1:547756) was not seen in the scan although its parent (1:545059) and previous (1:547755) refer to it. Check any previous errors.<br />There are 10 rows in 1 pages for object ‘tblCameoUKGroupDescriptions’.<br />DBCC results for ‘tblPRFSRByDistrictAndCameoFinancialONLY’.<br />There are 24 rows in 1 pages for object ‘tblPRFSRByDistrictAndCameoFinancialONLY’.<br />DBCC results for ‘tblPRFSRByDistrictAndBandONLY’.<br />There are 23 rows in 1 pages for object ‘tblPRFSRByDistrictAndBandONLY’.<br />DBCC results for ‘tblCameoFinancialGroupDescriptions’.<br />There are 7 rows in 1 pages for object ‘tblCameoFinancialGroupDescriptions’.<br />DBCC results for ‘tblEditStatus’.<br />There are 7 rows in 1 pages for object ‘tblEditStatus’.<br />DBCC results for ‘tblEurodirect’.<br /> The repair level on the DBCC statement caused this repair to be bypassed.<br /> The repair level on the DBCC statement caused this repair to be bypassed.<br /> The repair level on the DBCC statement caused this repair to be bypassed.<br /> The repair level on the DBCC statement caused this repair to be bypassed.<br /> The repair level on the DBCC statement caused this repair to be bypassed.<br /><br />
Since index ID 5 and 6 indicate nonclustered indexes, you might be able to DROP and reCREATE them. Correction! I would start with index ID 5 and see if this will do it.
—
Frank Kalis
SQL Server MVP
http://www.insidesql.de
How do I know it is a non clustered index or clustered index
A clustered index always has an index ID of 1 —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
Thank you for the info Frank
]]>