July 23, 2012 at 5:22 am
Hi All,
I'm struggling to bring a database online. The database was in in suspect state for some reason. So I changed it to EMERGENCY mode and ran DBCC CHECKDB command and it finished with few errors. I wanted to repair it using REPAIR_ALLOW_DATA_LOSS and I tried to put the database in single user mode with command SET SINGLE_USER WITH ROLLBACK IMMEDIATE. The database doesn't change to single user mode, the query never completes, it ran for like 2 days over the weekend. Please help me bringing the database back to normal.
The database size is around 150GB. It's a LIVE database :crying:
July 23, 2012 at 5:25 am
What error messages from DBCC CHECKDB do you get and do you have backups of the database in question?
I take it with running "REPAIR_ALLOW_DATA_LOSS" that your not to bothered about the data and anything that is lost can be reproduced.
July 23, 2012 at 5:37 am
Anthony, yes I have backup from the night before and also saved the T-log backups.
Unfortunately I don't remember the exact error messages from CHECKDB query. I got something like page couldn't be processed..
I thought REPAIR_ALLOW_DATA_LOSS is the best way to recover the database with minimal data loss. But can't run it with database in emergency state.
July 23, 2012 at 5:41 am
Run DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS and post the results.
July 23, 2012 at 5:43 am
there's not a lot we can do without the error messages, please provide them as Anthony has advised
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2012 at 5:43 am
anthony.green (7/23/2012)
Run DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS and post the results.
Running it. Will post the results soon.
July 23, 2012 at 5:53 am
Got backups? It's very likely that restoring from backup will be the best solution here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2012 at 7:02 am
Msg 5282, Level 16, State 2, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data), page (38175:9). The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
Msg 5282, Level 16, State 2, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data), page (40484:9). The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
Msg 5282, Level 16, State 2, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data), page (40485:9). The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332545) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332546) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332547) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332548) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332549) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332550) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332551) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332552) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332553) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332554) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332555) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332556) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332557) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332558) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332559) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332560) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332561) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332562) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332563) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332564) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332565) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332566) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332567) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332568) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332569) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332570) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332571) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332572) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332573) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332574) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:16332575) allocated to object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). The previous link (1:19308099) on page (1:16332544) does not match the previous page (1:16332545) that the parent (1:19305759), slot 59 expects for this page.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Page (1:16332544) is missing a reference from previous page (1:19308099). Possible chain linkage problem.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 58 refers to child page (1:16332545) and previous child (1:16332546), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 57 refers to child page (1:16332546) and previous child (1:16332547), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 56 refers to child page (1:16332547) and previous child (1:16332548), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 55 refers to child page (1:16332548) and previous child (1:16332549), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 54 refers to child page (1:16332549) and previous child (1:16332550), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 53 refers to child page (1:16332550) and previous child (1:16332551), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 52 refers to child page (1:16332551) and previous child (1:16332552), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 51 refers to child page (1:16332552) and previous child (1:16332553), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 50 refers to child page (1:16332553) and previous child (1:16332554), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 49 refers to child page (1:16332554) and previous child (1:16332555), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 48 refers to child page (1:16332555) and previous child (1:16332556), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 47 refers to child page (1:16332556) and previous child (1:16332557), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 46 refers to child page (1:16332557) and previous child (1:16332558), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 45 refers to child page (1:16332558) and previous child (1:16332559), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 44 refers to child page (1:16332559) and previous child (1:16332560), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 43 refers to child page (1:16332560) and previous child (1:16332561), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 42 refers to child page (1:16332561) and previous child (1:16332562), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 41 refers to child page (1:16332562) and previous child (1:16332563), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 40 refers to child page (1:16332563) and previous child (1:16332564), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 39 refers to child page (1:16332564) and previous child (1:16332565), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 38 refers to child page (1:16332565) and previous child (1:16332566), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 37 refers to child page (1:16332566) and previous child (1:16332567), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 36 refers to child page (1:16332567) and previous child (1:16332568), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 35 refers to child page (1:16332568) and previous child (1:16332569), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 34 refers to child page (1:16332569) and previous child (1:16332570), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 33 refers to child page (1:16332570) and previous child (1:16332571), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 32 refers to child page (1:16332571) and previous child (1:16332572), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 31 refers to child page (1:16332572) and previous child (1:16332573), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Index node page (1:19305759), slot 30 refers to child page (1:16332573) and previous child (1:16332574), but they were not encountered.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Page (1:16332574) was not seen in the scan although its parent (1:19305759) and previous (1:19308069) refer to it. Check any previous errors.
Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). The next pointer of (1:19308068) refers to page (1:16332575). Neither (1:16332575) nor its parent were encountered. Possible bad chain linkage.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data). Page (1:19308069) is missing a reference from previous page (1:19308068). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 68 consistency errors in table 'PrincipalObjectAccess' (object ID 706101556).
Msg 2533, Level 16, State 2, Line 1
Table error: page (1:356829) allocated to object ID 727009671, index ID 6, partition ID 72057597246636032, alloc unit ID 72057597501046784 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 2, Line 1
Table error: page (1:356830) allocated to object ID 727009671, index ID 6, partition ID 72057597246636032, alloc unit ID 72057597501046784 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 727009671, index ID 6, partition ID 72057597246636032, alloc unit ID 72057597501046784 (type In-row data). Index node page (1:35029), slot 6 refers to child page (1:356829) and previous child (1:356830), but they were not encountered.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 727009671, index ID 6, partition ID 72057597246636032, alloc unit ID 72057597501046784 (type In-row data). Page (1:356830) was not seen in the scan although its parent (1:35029) and previous (1:365538) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'MetadataSchema.Attribute' (object ID 727009671).
CHECKDB found 0 allocation errors and 72 consistency errors in database 'XXXXXX'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXXX).
July 23, 2012 at 7:16 am
USE MASTER
GO
EXEC sp_resetstatus '<databasename>'
GO
ALTER DATABASE <databasename> SET EMERGENCY
GO
ALTER DATABASE <databasename> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (<databasename>, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE <databasename> SET MULTI_USER
GO
Or restore the database from a backup and apply the nessesary transaction logs.
July 23, 2012 at 7:24 am
Anthony,
I was following that process. But the database doesn't change to single user mode for some reason.
I think I'll have to go for restoring from the backup ..
July 23, 2012 at 7:25 am
farushaik (7/23/2012)
Msg 5282, Level 16, State 2, Line 1Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data), page (38175:9). The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
Table error: Object ID 727009671, index ID 6, partition ID 72057597246636032, alloc unit ID 72057597501046784 (type In-row data). Page (1:356830) was not seen in the scan although its parent (1:35029) and previous (1:365538) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'MetadataSchema.Attribute' (object ID 727009671).
CHECKDB found 0 allocation errors and 72 consistency errors in database 'XXXXXX'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXXX).
It's a non clustered index, can you supply more detail on the index (object id 706101556) and the table (object id 727009671)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2012 at 7:26 am
farushaik (7/23/2012)
Anthony,I was following that process. But the database doesn't change to single user mode for some reason.
I think I'll have to go for restoring from the backup ..
see my post above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2012 at 7:41 am
If it's suspect (as opposed to just corrupt), it won't return to normal without running CheckDB with repair allow data loss (the only option valid when the DB is suspect). If you can't run CheckDB with repair allow data loss (it's by no means guaranteed that you can, Emergency mode is a last resort), then you won't be able to fix this.
I recommend restore from backup. If you have full and log backups than you can restore to almost point of failure (or point of failure if you can take a log backup) without data loss.
Emergency mode repair is a last resort for when there are no backups and you ahve to get as much as possible recovered. It should not be the first approach to recovering from a suspect DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2012 at 8:01 am
Perry Whittle (7/23/2012)
farushaik (7/23/2012)
Msg 5282, Level 16, State 2, Line 1Table error: Object ID 706101556, index ID 2, partition ID 72057597246242816, alloc unit ID 72057597500653568 (type In-row data), page (38175:9). The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
Table error: Object ID 727009671, index ID 6, partition ID 72057597246636032, alloc unit ID 72057597501046784 (type In-row data). Page (1:356830) was not seen in the scan although its parent (1:35029) and previous (1:365538) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'MetadataSchema.Attribute' (object ID 727009671).
CHECKDB found 0 allocation errors and 72 consistency errors in database 'XXXXXX'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (XXXXX).
It's a non clustered index, can you supply more detail on the index (object id 706101556) and the table (object id 727009671)
What exactly do you mean by more detail?
July 23, 2012 at 8:01 am
GilaMonster (7/23/2012)
If it's suspect (as opposed to just corrupt), it won't return to normal without running CheckDB with repair allow data loss (the only option valid when the DB is suspect). If you can't run CheckDB with repair allow data loss (it's by no means guaranteed that you can, Emergency mode is a last resort), then you won't be able to fix this.I recommend restore from backup. If you have full and log backups than you can restore to almost point of failure (or point of failure if you can take a log backup) without data loss.
Emergency mode repair is a last resort for when there are no backups and you ahve to get as much as possible recovered. It should not be the first approach to recovering from a suspect DB.
Thanks for that. I'll keep remember this point 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply