| Author |
 Topic  |
|
|
b5white
Starting Member
18 Posts |
 Posted - 01/20/2011 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 01/20/2011 : 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 - 01/20/2011 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 01/20/2011 : 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 - 01/20/2011 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 01/20/2011 : 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
USA
4 Posts |
Posted - 02/18/2011 : 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
India
8 Posts |
Posted - 02/21/2011 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 02/22/2011 : 03:18:22
|
No, data is not always accessible and recovery is not always possible.
--
Gail Shaw
SQL Server MVP |
|
|
|
rahulrajpal08
Starting Member
India
8 Posts |
Posted - 02/23/2011 : 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
USA
35007 Posts |
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 02/23/2011 : 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
USA
35007 Posts |
|
|
rahulrajpal08
Starting Member
India
8 Posts |
Posted - 02/24/2011 : 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 - 03/14/2011 : 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
USA
35007 Posts |
|
|
jiayue
Starting Member
4 Posts |
|
| |
Topic |
|
|
|
checkdb errors
