March 6, 2017 at 4:42 am
Hi all, my last checkdb return this error for 6 tables:
this is an example:
Table error: Multiple IAM pages for object ID 305268216, index ID 1, partition ID 72058173212065792, alloc unit ID 72058197905440768 (type In-row data) contain allocations for the same interval. IAM pages (9208:20) and (9208:12).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'xxxxxx (object ID 305268216).
dbcc ind confirms :
dbcc ind (0,'xxxxxx,1)
9208 12 NULL NULL 305268216 1 1 72058173212065792 In-row data 10 NULL 9208 20 0 0
9208 14 9208 12 305268216 1 1 72058173212065792 In-row data 2 2 0 0 0 0
9208 13 9208 12 305268216 1 1 72058173212065792 In-row data 2 1 0 0 0 0
9208 18 9208 12 305268216 1 1 72058173212065792 In-row data 1 0 0 0 0 0
9208 20 NULL NULL 305268216 1 1 72058173212065792 In-row data 10 NULL 0 0 9208 12
5 of these 6 tables have 0 rows, 1 with 6000+, could i simply get rid of this error by rebuilding index?
I'd like to avoid a database restore.
Can i check status of these tables without run a full checkdb? i tried
DBCC CHECKTABLE ( 'xxxxxxx',1 ) WITH ALL_ERRORMSGS , EXTENDED_LOGICAL_CHECKS
but it returns all ok
March 6, 2017 at 5:43 am
What's the full, unedited output ofDBCC CheckDB WITH NO_INFOMSGS;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2017 at 6:22 am
edited only table name, can run checkdb now, copy errors from maint_plan log:
Failed: (-1073548784) Executing the query "DBCC CHECKDB(N'dbxxxxxxxx') WITH NO_INFOMSGS
" failed with the following error: "
Table error: Multiple IAM pages for object ID 305268216, index ID 1, partition ID 72058173212065792, alloc unit ID 72058197905440768 (type In-row data) contain allocations for the same interval. IAM pages (9208:20) and (9208:12).
Table error: Multiple IAM pages for object ID 427032676, index ID 1, partition ID 72058161627725824, alloc unit ID 72058185428762624 (type In-row data) contain allocations for the same interval. IAM pages (8536:20) and (8536:12).
Table error: Multiple IAM pages for object ID 704605672, index ID 1, partition ID 72058163597672448, alloc unit ID 72058187459657728 (type In-row data) contain allocations for the same interval. IAM pages (9218:13) and (9218:21).
Table error: Multiple IAM pages for object ID 939034500, index ID 1, partition ID 72058161754210304, alloc unit ID 72058185559179264 (type In-row data) contain allocations for the same interval. IAM pages (9428:22) and (9428:12).
Table error: Multiple IAM pages for object ID 947342092, index ID 1, partition ID 72057904322772992, alloc unit ID 72057917467590656 (type In-row data) contain allocations for the same interval. IAM pages (4446:264836) and (4446:255320).
Table error: Multiple IAM pages for object ID 1187367368, index ID 1, partition ID 72058171107049472, alloc unit ID 72058195554009088 (type In-row data) contain allocations for the same interval. IAM pages (7855:11) and (7855:20).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'xxxxxxx1' (object ID 305268216).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'xxxxxxx'2 (object ID 427032676).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'xxxxxxx'3 (object ID 704605672).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'xxxxxxx4' (object ID 939034500).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'xxxxxxx5' (object ID 947342092).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'xxxxxxx6' (object ID 1187367368).
CHECKDB found 9 allocation errors and 0 consistency errors in database 'dbxxxxxxxx'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbxxxxxxxx).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
March 6, 2017 at 6:35 am
DBCC CheckAlloc is probably what you need to pick those up without a full checkDB.
Do you really have over 9000 files in that database?
Index rebuild won't work. Drop clustered index and recreate *might* work, might not.
Safer would be drop and recreate the empty tables, copy the data for the one with data somewhere else (just another table) and drop, recreate, put data back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2017 at 6:46 am
I figured you had already gotten this one Gail.
sgt500: She is one of the best around at dealing with database corruptions. Pay attention and do as she says and you will come out of this the best way possible. And be sure to thank her for her help when you are done. Companies with corruptions often pay a LOT of money to get help fixing them. Good luck with it!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2017 at 7:24 am
just tried to rebuild the pk of an empty table (the one in the first example)
after the rebuild dbcc ind (0,'xxxxxx,1) returns different result:
9208 17 NULL NULL 305268216 1 1 72058211345760256 In-row data 10 NULL 0 0 0 0
9208 16 9208 17 305268216 1 1 72058211345760256 In-row data 1 0 0 0 0 0
seems a good news....is it? Ther's no more multiple IAM pages (type 10 ) for this object
and yes, this db has 18k+ files....i've inherited this situation, is a multitenant application
dbcc checkalloc is only database level, i can't run this without degrade performance, my idea is to restore the db on another server and run check alloc on the restored one. It will take several hours or days...
for now ty very much
March 6, 2017 at 7:35 am
sgt500 - Monday, March 6, 2017 7:24 AMjust tried to rebuild the pk of an empty table (the one in the first example)
after the rebuild dbcc ind (0,'xxxxxx,1) returns different result:
9208 17 NULL NULL 305268216 1 1 72058211345760256 In-row data 10 NULL 0 0 0 0
9208 16 9208 17 305268216 1 1 72058211345760256 In-row data 1 0 0 0 0 0seems a good news....is it? Ther's no more multiple IAM pages (type 10 ) for this object
and yes, this db has 18k+ files....i've inherited this situation, is a multitenant application
dbcc checkalloc is only database level, i can't run this without degrade performance, my idea is to restore the db on another server and run check alloc on the restored one. It will take several hours or days...
for now ty very much
1) Seems you didn't get to read my post before taking action. Didn't Gail tell you to drop and recreate the empty tables?
2) Hours or days to run dbcc checks? Are you patched up? Some SERIOUS flaws in that subsystem have been fixed in the last year or two. Also, there are some schema choices that can TOTALLY FUBAR those checks. Search Argenis Fernandez SQL Server checkdb and you will find some good guidance. Search Paul Randal too.
3) 18K+ database files? Ohhh, I would LOVE to take a crack at that system!! I bet there is all KINDS of stuff to be found therein!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2017 at 7:40 am
TheSQLGuru - Monday, March 6, 2017 7:35 AMDidn't Gail tell you to drop and recreate the empty tables?
Yup, because rebuild can easily run into corruption because it reads the existing index to create the new one. Drop and recreate, don't rebuild the clustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2017 at 8:22 am
GilaMonster - Monday, March 6, 2017 7:40 AMTheSQLGuru - Monday, March 6, 2017 7:35 AMDidn't Gail tell you to drop and recreate the empty tables?
Yup, because rebuild can easily run into corruption because it reads the existing index to create the new one. Drop and recreate, don't rebuild the clustered index.
Ok, did a drop-create,
now db ind returns 0 rows, no errors
I'll test the drop-create table for the ones with data in the restored db.
about checkdb running time:
in production server it tooks about 15h to finish, its a 9tb database. for this reason checkdb runs 1 time every 3 weeks
March 9, 2017 at 7:51 am
Db restored on the test server, checkdb has found the same errors as expected
I did things suggested by Gail, (drop-create the index / tables), after this, the chekdb has not found errors
ty all for the tips
a weird thing, on test server checdb runs in 1.15 h against 15h on the production server, Can the load on production server slow down checkdb so much?
ps: on test server i run this command:
dbcc checkdb ('dbxxxx') with ALL_ERRORMSGS , EXTENDED_LOGICAL_CHECKS
March 9, 2017 at 12:15 pm
sgt500 - Thursday, March 9, 2017 7:51 AMa weird thing, on test server checdb runs in 1.15 h against 15h on the production server, Can the load on production server slow down checkdb so much?
Absolutely!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply