November 30, 2009 at 6:58 pm
Here are the ending lines of the DBCC CheckDB that I ran:
CHECKDB found 1 allocation errors and 0 consistency errors in database 'foo'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (foo noindex).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So, going up through the text output, I find the following 2 areas that look suspect:
(1)
DBCC results for 'tbl099'.
There are 0 rows in 1 pages for object 'tbl099'.
DBCC results for 'tbl101'.
There are 17 rows in 1 pages for object 'tbl101'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:986572) in database ID 7 is allocated in the SGAM (1:511233) and PFS (1:978648), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
DBCC results for 'tbl202'.
There are 0 rows in 0 pages for object 'tbl202'.
DBCC results for 'tb205'.
There are 0 rows in 1 pages for object 'tbl205'.
(2)
DBCC results for 'tbl455'.
There are 0 rows in 0 pages for object 'tbl455'.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 1251027738)' (object ID 1251027738).
DBCC results for 'tbl988'.
There are 300 rows in 5 pages for object 'tbl988'.
For (1) I selected * from tbl101 and it wasn't much data at all. The table is small and simple, with only 1 index, no constraints or triggers. I scripted the table, recreated it with a new name, selected the data from the original table to the new table, dropped the old table, and renamed the new table.
For (2), I did a "select * from sysobjects where id = 1251027738") and get nothing back. Same with "select * from sysindexes where id = 1251027738" -- nothing.
After doing these two things, I reran the DBCC CheckDB and was surprised to find that the results for (1) had changed, but the results for (2) were exactly the same. New results for (1):
Here are the ending lines of the DBCC CheckDB that I ran:
CHECKDB found 1 allocation errors and 0 consistency errors in database 'foo'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (foo noindex).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So, going up through the text output, I find the following 2 areas that look suspect:
(1)
DBCC results for 'tbl099'.
There are 0 rows in 1 pages for object 'tbl099'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:986572) in database ID 7 is allocated in the SGAM (1:511233) and PFS (1:978648), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
DBCC results for 'tbl202'.
There are 0 rows in 0 pages for object 'tbl202'.
DBCC results for 'tb205'.
There are 0 rows in 1 pages for object 'tbl205'.
Notice that tbl101 is not listed above the problem line anymore, it has moved up so that it appears that it now belongs to the "looksee" for tbl099. So -- I did the same thing, tbl099 had no data in it anyhow, so I just dropped and recreated it. Then ran the DBCC CheckDB again...now the message for (1) shows the same Msg 8906, but it comes right after the entry that was previously above tbl099. Tbl099 has moved down the list and is nowhere near the error msg.
Then I looked closer at the error message. In all 3 instances, the Page, SGAM, PFS, the whole thing was the same. Always the same IDs in each section, and this message never changed no matter what time I ran it, the entire message is the same verbatim each time.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:986572) in database ID 7 is allocated in the SGAM (1:511233) and PFS (1:978648), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
So, what is this telling me? And is there an easier way to fix it than porting all the data out and back in?
Oh - and DBCC CheckDB repair_allow_data_loss does not fix the problem. Tried that. 🙂
Thanks for any help you can offer.
Mindy
Mindy Curnutt
Sr. SQL Server DBA / Hardware Infrastructure Architect
TMW Systems, Inc.
twitter: @sqlgirl
November 30, 2009 at 7:20 pm
This is output from a 2000 server, right? FYI this is the 2005+ specific corruption forum. But I'll answer the question anway.
My guess is you have multiple CPUs and Enterprise Edition so CHECKDB is running in parallel. In that case it depends which CPU happens to process the set of 'facts' that lead to the 8906 being detected. It's also not a table-specific error, so can appear anywhere in the output. Nothing strange there.
The object ID being complained about is the one stamped in the page header of of page (1:986572). It's a non-existent table, as you've discovered - most likely one that was dropped.
The error is telling you that there's a page allocated from a mixed-extent (one where pages can be allocated to different objects), but no object has it actually tracked in an allocation bitmap (IAM page).
As for getting rid of this, in 2000 I didn't write the repair code and there were lots of holes. If memory serves, I fixed a lot of bugs in the repairs for these kinds of allocation errors when I rewrote everything for 2005.
So - repair on 2000 isn't going to fix it for you, and there's no documented, supported, safe way of just removing that page. You could try shrinking the database down below that page ID - that will deallocate it and remove the problem, but with the unwanted side-effects of the shrink operation.
We could do a hex-edit of the PFS page to unallocate the page...
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 30, 2009 at 7:28 pm
Very satisfying answer. OK. I will try the shrink, and then try having them restore a backup to SQL 2005 and see if I can fix it there. If it fixes there, then I will suggest the solution is to upgrade.
As always, very, very helpful. Thank you for your expertise.
Sorry, I didn't see the SQL Server 2005 Tree, now I see it. Won't make that mistake again.
Mindy Curnutt
Sr. SQL Server DBA / Hardware Infrastructure Architect
TMW Systems, Inc.
twitter: @sqlgirl
November 30, 2009 at 7:40 pm
No problem Mindy - you could have just sent me email - I would have answered you 🙂
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 2, 2009 at 7:37 pm
Tried backing up from SQL 2000 and restoring to SQL 2008, no dice. Restored in suspect mode.
Next step, take backup and restore to SQL 2000, detach the mdf and ldf, move to SQL 2008 and attach. That worked.
Then run DBCC CheckDB with repair_allow_data_loss, fixed the corruption.
Detached mdf and ldf, moved back to SQL 2000, could not attach.
START OVER
Then...took a copy of the mdf and ldf from SQL 2000, attached into SQL 2005.
Ran DBCC CheckDB with repair_allow_data_loss, fixed the corruption.
Took a BACKUP of the database (as a .bak file) and restored back to SQL 2000 -- I wasn't in on this part, I'm not sure why he chose to try this, as we hadn't tried this w/SQL 2008's copy, but oh well. It worked!
Then ran DBCC CheckDB from SQL 2000 -- the darn corruption was BACK! Argh!
Just for kicks, ran DBCC CheckDB with repair_allow_data_loss from SQL 2000, and this time it fixed the corruption.
So now the DB is clean.
Whew, what nightmare, and what a convoluted way of going about things.
But -- that was all in "test", let's see if we can do it again and it works a 2nd time!!!!
Mindy Curnutt
Sr. SQL Server DBA / Hardware Infrastructure Architect
TMW Systems, Inc.
twitter: @sqlgirl
December 2, 2009 at 7:53 pm
How did the restore of the 2005 database to the 2000 server work? That's not possible at all. From the sequence of errors, it looks like the restore didn't actually do anything - as the corruption wouldn't magically reappear again. You were running on the original 2000 database.
Are you sure the 2000 shrink actually got the database down below the broken page ID? If it had, it will have removed the corruption because that page physically wouldn't exist any more - as the file would be physically smaller than that page ID...
Hmm - at least the 2005 and 2008 CHECKDBs behave as they're supposed to. As I said, the 2000 one was a little dodgy sometimes.
Cheers
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 2, 2009 at 8:22 pm
I'll talk to the guy who did it tomorrow.
I questioned the backup/restore, I didn't think that would work from 2005 to 2000.
I do know that if you leave the DB in 8.0 compat mode, that it is possible to detach from 2005 and reattach to 2000 (the mdf/ldf), I've done it before, but I'm not sure what the magic combination to get it to work was.
I've even used 2000 to fix/recreate a corrupted ldf file from a SQL 2005 DB in 8.0 mode, where 2005 would not do it no matter what switches/etc I tried.
That's not what he said he did though, he specifically said he restored a .bak.
Like I said, I will question him on his process. The reappearance of the corruption also seemed wrong to me too, I agree.
Mindy Curnutt
Sr. SQL Server DBA / Hardware Infrastructure Architect
TMW Systems, Inc.
twitter: @sqlgirl
December 2, 2009 at 8:33 pm
No, that's not possible either. The upgrade changes the database so 2000 can't understand it and bumps the version number from 539 to 610 or 611. Compat mode has nothing to do with physical version number.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 3, 2009 at 11:28 am
Well darn you Paul. I hate it when I'm wrong. 🙂
OK...so I went back and questioned this guy this AM, and followed his steps, and turns out he had grabbed the original backup from 2000, thinking it was the 2005 backup. So he just restored the backup to 2000 that was what we already had on 2000, and was not fixed.
But here's the good news out of all of this....somehow the SQL 2000 DBCC CheckDB is now able to fix the corruption, whereas before it would not. I did do the shrinking (pretty aggressive one at that), and afterward there was still corruption present, but I had not thought to try 1 more DBCC CheckDB with repair_allow_data_loss after that. I'm not sure if taking a backup and then restoring the backup has anything to do with it now succeeding either - maybe that shuffled things around?
So -- we are going to restore a newer backup to SQL 2000 (post shrink) and see if we can fix it using DBCC CheckDB with r-a-d-l and if that will work too. And then tonight, he is going to try to fix production this way on SQL 2000, and if that doesn't work, then depending upon the results of the backup/restore/DBCC attempt, he may try that on production too.
So weird.
And on a separate note, I really do remember many times taking DBs back and forth from 2000 to 2005 and back to 2000, but wouldn't you know, today it is not working. I have a question in to someone I used to work with to make sure I'm not getting some kind of dementia. 🙂
Mindy Curnutt
Sr. SQL Server DBA / Hardware Infrastructure Architect
TMW Systems, Inc.
twitter: @sqlgirl
December 3, 2009 at 11:47 am
The backup wouldn't change anything (it never does) but the shrink might have in such a way that the repair would work. 2000 is a bit dodgy as I said.
You definitely can't restore a 2005 backup to a 2000 server - it's just not physically possible and never has been. SQL Server isn't up-level compatible with database formats. See my blog post Msg 602, Level 21, State 50, Line 1
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 3, 2009 at 8:54 pm
OK. Doing the shrink (aggressively, as much as possible), and then afterward, checking for corruption (still there), and THEN running DBCC CheckDB r-a-d-l did the trick -- all in SQL 2000. Database is now clean.
Thanks for your help Paul. What a bunch of hurdles that was, but we got it done!
Mindy Curnutt
Sr. SQL Server DBA / Hardware Infrastructure Architect
TMW Systems, Inc.
twitter: @sqlgirl
December 3, 2009 at 9:55 pm
No problem - always enjoy this stuff.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply