checkdb errors - SQL Server Forums

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

General SQL Server Forums

Data Corruption Issues

checkdb errors

Author

Topic

b5white
Starting Member

18 Posts

Posted - 2011-01-20 : 11:42:59

Server went down and has been restored. DB is suspect. Our most recent DB backup that is good is several weeks old. Other backups since then won't even attach.
We want to try to recover this suspect DB to retain the more recent data.

Is there any hope?

In single user, emergency mode,
DBCC Checkdb ('checkimaging', REPAIR_ALLOW_DATA_LOSS) gives

Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:2216112) has invalid PFS_PAGE page header values. Type is 0. Check type, alloc unit ID and page ID on the page.
DBCC results for 'CheckImaging'.
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 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2216112) to (1:2224199). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2224200) to (1:2232287). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2232288) to (1:2240375). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2240376) to (1:2248463). See other errors for cause.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2216112) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2216112) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page (1:2224200). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12585225 and -6.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2232288) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2240376) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
CHECKDB found 8 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1036 rows in 9 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 147 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 171 rows in 3 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 1036 rows in 12 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 147 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 171 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 27 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 233 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 341 rows in 8 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1447 rows in 33 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 20 rows in 8 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 458 rows in 9 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 656 rows in 4 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 623 rows in 187 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 19 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 245 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 1056 rows in 12 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'Blobs'.
There are 0 rows in 1 pages for object "Blobs".
DBCC results for 'StatementAccountGroups'.
There are 1 rows in 1 pages for object "StatementAccountGroups".
CHECKDB found 8 allocation errors and 1 consistency errors in database 'CheckImaging'.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-20 : 12:16:44

Run the following, post the full and complete results


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

2nd corruption situation in 2 months? I think you may want to do some diagnoses on your IO subsystem....

--
Gail Shaw
SQL Server MVP

b5white
Starting Member

18 Posts

Posted - 2011-01-20 : 12:29:52

Gail,

Thanks for the help.

> 2nd corruption situation in 2 months? I think you may want to
> do some diagnoses on your IO subsystem....

This is a different client. We are working on a newsletter
article so that others can learn from these mistakes.
We'd like to require that they try to restore from backup
once a quarter, but we don't have that kind of clout.

>
> DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:2216112) 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 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2216112) to (1:2224199). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2224200) to (1:2232287). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2232288) to (1:2240375). See other errors for cause.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 7 pages from (1:2240376) to (1:2248463). See other errors for cause.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2216112) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2216112) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page (1:2224200). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12585225 and -6.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2232288) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7021218953279242240 (type Unknown), page ID (1:2240376) contains an incorrect page ID in its page header. The PageId in the page header = (2573:539042151).
CHECKDB found 8 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 8 allocation errors and 1 consistency errors in database 'CheckImaging'.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-20 : 12:56:40

Not repairable. Restore from last good backup.

You have large portions of the database that are inaccessible because the allocation pages are damaged and you have damaged system tables.

--
Gail Shaw
SQL Server MVP

b5white
Starting Member

18 Posts

Posted - 2011-01-20 : 15:36:25

OK, thanks for looking at it.

I can already see that I am missing three minor data tables.
Copied the support tables with settings and so forth.
Will try to copy the rest of the data tonight.

Thanks,
Brad.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-20 : 15:48:08

Restore from your last good 'backup' There will very likely be portions of other tables that are missing, there's system table damage, etc. Exporting may well leave you with inconsistent data.

--
Gail Shaw
SQL Server MVP

addisionphilip
Starting Member

4 Posts

Posted - 2011-02-18 : 01:12:18

Run this command DBCC CHECKDB (<DB Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, it may helps to restore your SQL server database.

unspammed

rahulrajpal08
Starting Member

8 Posts

Posted - 2011-02-21 : 13:04:22

frequent corruption in the database can result into serious hazards. Find out the exact reason for your database corruption. To summarize there are two types of database corruption- logical and physical. However, dont loose hope, data is always accessible. To read all the corruption reasons, here is a very good link i found on the Internet:
unspammed

Database expert

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-22 : 03:18:22

No, data is not always accessible and recovery is not always possible.

--
Gail Shaw
SQL Server MVP

rahulrajpal08
Starting Member

8 Posts

Posted - 2011-02-23 : 14:04:27

I strongly disagree with Mr. Gail Shaw. I am 100% sure that the records are recoverable. Mr. Gail Shaw, you might not know that there are numerous database repair products now-a-days available on Internet. These products provide complete recovery and complete repair of database. In case you wish to read about these products, here is a very good link:
unspammed

Database expert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-23 : 14:59:29

What makes you think Gail should be address as Mr.?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-23 : 23:57:57

quote:
Originally posted by rahulrajpal08

I strongly disagree with Mr. Gail Shaw. I am 100% sure that the records are recoverable.

Really? Want to bet some real money on it?

quote:
Mr. Gail Shaw, you might not know that there are numerous database repair products now-a-days available on Internet. These products provide complete recovery and complete repair of database.

No, they don't. Under some circumstances they may be able to recover some data, but there are corruption cases (including ones I've experienced personally) where the so-called guaranteed data recovery tools recovery little to nothing.

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-24 : 00:02:57

I want in on this bet! I take Gail's side. Instant money!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rahulrajpal08
Starting Member

8 Posts

Posted - 2011-02-24 : 02:15:03

Gail: I posted the above based on my experience. I have been using these software and they have recovered all my database records, each time I have encountered any logical corruption.
Tara: Let Gail use the software and then we will surely have a bet.:-)

Database expert

jiayue
Starting Member

4 Posts

Posted - 2011-03-14 : 16:15:30

Hi Gail, I'm getting similar results after running the script of
DBCC Checkdb ('DBNAME',repair_rebuild) with no_infomsgs, all_errormsgs
GO

--------------------------------------------
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1146591273) of row (object_id=1146591273,parameter_id=1) in sys.parameters does not have a matching row (object_id=1146591273) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1146591273) of row (object_id=1146591273,parameter_id=2) in sys.parameters does not have a matching row (object_id=1146591273) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1146591273) of row (object_id=1146591273,parameter_id=3) in sys.parameters does not have a matching row (object_id=1146591273) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1146591273) of row (object_id=1146591273,parameter_id=4) in sys.parameters does not have a matching row (object_id=1146591273) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1146591273) of row (object_id=1146591273,parameter_id=5) in sys.parameters does not have a matching row (object_id=1146591273) in sys.objects.
CHECKDB found 0 allocation errors and 5 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 5 consistency errors in database 'DBNAME'.

Can you take a look for me and point me a direction?

thanks a lot!

JJ

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-14 : 16:17:19

JJ, please start a new topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jiayue
Starting Member

4 Posts

Posted - 2011-03-14 : 16:31:08

Will do.

quote:
Originally posted by tkizer

JJ, please start a new topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources