SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 checkdb errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

b5white
Starting Member

18 Posts

Posted - 01/20/2011 :  11:42:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/20/2011 :  12:16:44  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

b5white
Starting Member

18 Posts

Posted - 01/20/2011 :  12:29:52  Show Profile  Reply with Quote
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'.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/20/2011 :  12:56:40  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

b5white
Starting Member

18 Posts

Posted - 01/20/2011 :  15:36:25  Show Profile  Reply with Quote
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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/20/2011 :  15:48:08  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

addisionphilip
Starting Member

USA
4 Posts

Posted - 02/18/2011 :  01:12:18  Show Profile  Visit addisionphilip's Homepage  Reply with Quote
Run this command DBCC CHECKDB (<DB Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, it may helps to restore your SQL server database.

unspammed
Go to Top of Page

rahulrajpal08
Starting Member

India
8 Posts

Posted - 02/21/2011 :  13:04:22  Show Profile  Visit rahulrajpal08's Homepage  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/22/2011 :  03:18:22  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
No, data is not always accessible and recovery is not always possible.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

rahulrajpal08
Starting Member

India
8 Posts

Posted - 02/23/2011 :  14:04:27  Show Profile  Visit rahulrajpal08's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37119 Posts

Posted - 02/23/2011 :  14:59:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 02/23/2011 :  23:57:57  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37119 Posts

Posted - 02/24/2011 :  00:02:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rahulrajpal08
Starting Member

India
8 Posts

Posted - 02/24/2011 :  02:15:03  Show Profile  Visit rahulrajpal08's Homepage  Reply with Quote
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
Go to Top of Page

jiayue
Starting Member

4 Posts

Posted - 03/14/2011 :  16:15:30  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37119 Posts

Posted - 03/14/2011 :  16:17:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jiayue
Starting Member

4 Posts

Posted - 03/14/2011 :  16:31:08  Show Profile  Reply with Quote
Will do.

JJ

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000