allocation errors and 1 consistency errors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

allocation errors and 1 consistency errors

Author

Topic

susuown
Starting Member

3 Posts

Posted - 2009-06-03 : 06:55:14

Hi,
I am getting following error when I did dbcc checkdb with one of my db. But I can still use my DB and application with out any problem.

Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:477192) has invalid PFS_PAGE page header values. Type is 0. Check type, alloc unit ID and page ID on the page.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 8966, Level 16, State 4, Line 1
Unable to read and latch page (1:477192) with latch type SH. UtilDbccVerifyPageId failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 2579, Level 16, State 1, Line 1
Table error: Extent (1:479112) in object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data) is beyond the range of this database.
Msg 2579, Level 16, State 1, Line 1
Table error: Extent (1:479136) in object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data) is beyond the range of this database.
Msg 2579, Level 16, State 1, Line 1
Table error: Extent (1:479144) in object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data) is beyond the range of this database.
Msg 2579, Level 16, State 1, Line 1
Table error: Extent (1:479160) in object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data) is beyond the range of this database.
Msg 2579, Level 16, State 1, Line 1
Table error: Extent (1:479352) in object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data) is beyond the range of this database.
CHECKDB found 5 allocation errors and 0 consistency errors in table 'sys.sysqnames' (object ID 90).
CHECKDB found 5 allocation errors and 1 consistency errors in database 'Mydb'.

I can repair this with the following,
dbcc CHECKALLOC('Mydb', REPAIR_ALLOW_DATA_LOSS)
go
dbcc CHECKDB('Mydb', REPAIR_ALLOW_DATA_LOSS)
go

However, I would like to know if this would cause any data loss.
I can use my db and application with out any problem even after the dbcc fix.

Your help will be greatly appreciated.
Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-03 : 11:35:08

The option is called REPAIR ALLOW DATA LOSS If that's the level that CheckDB requires then running it with that option will lose data.

However....
You've got errors in the system tables there. CheckDB cannot and will not repair those. You have irreparable corruption. That's why there's no recommendation as to the repair level required, which you usually get as one of the last lines of CheckDB. It's not there because no repair level will work here,
Take a look at this article. [url]http://www.sqlservercentral.com/articles/65804/[/url] There's a section towards the end on irreparable corruption and the options for fixing it.

--
Gail Shaw
SQL Server MVP

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-06-03 : 11:59:22

REPAIR_ALLOW_DATA_LOSS didn't do anything - it can't do anything with a corrupt PFS page. What actually happened was that the action of running CHECKDB flushed out your buffer pool and the next time the page was read from disk it was correct, so it appeared that CHECKDB fixed it.

I think you have a stale read problem on your IO subsystem or you have bad memory that's introducing in-memory corruptions. I recommend that you run memory and IO subsystem diagnostics.

Btw - you don't need to run DBCC CHECKALLOC and DBCC CHECKDB - CHECKDB includes all the CHECKALLOC functionality.

Thanks

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

susuown
Starting Member

3 Posts

Posted - 2009-06-03 : 21:52:49

Thank you so much for your immediate reply. I checked the article at http://www.sqlservercentral.com/articles/65804/,
it says

Corruption in the LOB pages

Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 181575685, index ID 1, partition ID 72057594145669120, alloc unit ID 72057594087800832 (type LOB data). The off-row data node at page (1:2444050), slot 0, text ID 901891555328 is not referenced.

This indicates that there are LOB (large object) pages that are not referenced by any data row. This can come about if there was corruption of the clustered index or heap and the damaged pages were deallocated.

If these are the only errors that CheckDB returns, then running repair with the Allow_Data_Loss will simply deallocate these pages. Since the data rows that the LOB data belongs to does not exist, this will not result in further data loss.

So, my biggest concern is, will my data be lost? What would you recommend for the solution? Restore from last backup before the corruption happened would be tedious.

Thanks once again,
Sushil

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-04 : 03:05:04

You don't have corruption in the LOB pages. The error that you list from my article is completely different to the error that you had. The section I wanted you to read from that article was a lot further down and is titled "Irreparable Corruption"

What your original post had was bad pageIDs
Table error: Extent (1:479144) in object ID 90, index ID 1, partition ID 281474982608896, alloc unit ID 72057594038583296 (type In-row data) is beyond the range of this database.

There was no mention of LOB pages at all in what you posted. Furthermore, as I mentioned, the corruption was in a system table and system tables are not repairable. Running checkDB with any repair level here would have done no repairs. It couldn't.

Paul gave a possible reason for why running CheckDB made the errors appear to go away.

If you're seeing more, different corruptions, post the output of CheckDB and we'll try and help.

--
Gail Shaw
SQL Server MVP

susuown
Starting Member

3 Posts

Posted - 2009-06-04 : 03:56:01

Thanks, yeah that's right. It means I will have to pump the data to new DB. I would choose that option. Is there any utilities available for data pump? Would you suggest me on how to pump data from existing DB to new DB? Any related link for the reference? I will try to google too.
Thanks a lot
Sushil

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-04 : 04:34:04

Before you do anything else, run CheckDB again (without any repair statement). Are there still errors? If not, then you may have some form of stale read or memory corruption, as Paul indicated.

If you still do get the errors, then, yes, you're going to have to recreate the DB.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources