January 28, 2009 at 4:58 am
Hello all,
I have a ms sql 2005 db. when i run dbcc checkdb, i get the error below.
DBCC results for 'PBEHolidayAverages'.
Msg 8928, Level 16, State 1, Line 2
Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 61504646152192 (type In-row data): Page (1:4413) could not be processed. See other errors for details.
Msg 8944, Level 16, State 16, Line 2
Table error: Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 342979622862848 (type In-row data), page (1:4413), row 6. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 89745 and 297.
Msg 8944, Level 16, State 16, Line 2
Table error: Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 342979622862848 (type In-row data), page (1:4413), row 6. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 89745 and 297.
There are 3679 rows in 137 pages for object "PBEHolidayAverages".
I then ran a select on the table "PBEHolidayAverages". I got the error as follows after which i am automatically disconnected with the db:
An inconsistency was detected during an internal operation in database 'CPS_EASTMAN'(ID:3332) on page (1:4413). Please contact technical support. Reference number 4.
I tried to detach and reattach the db but it is still the same. Can you please advise on proceedings.
Thanks.
A.
January 28, 2009 at 5:13 am
You have corruption in that table in your database - most likely caused by IO subsystem problems. You have a number of choices to recover from this:
1) restore from a backup
2) drop and recreate the table if possible
3) run the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB which will most likely deallocate that page and all the data on it (which means you'll lose data)
Do you have page checksums enabled? If not, you should do (ALTER DATABASE blah SET PAGE_VERIFY CHECKSUM) so that you can catch problems ASAP.
When was the last time you ran DBCC CHECKDB? Did anything untoward occur in between the two times?
Let us know what you're going to do and whether you find any IO subsystem issues (look in the SQL Server error log, Windows event logs, run IO diagnostics, check firmware levels etc etc)
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 28, 2009 at 6:13 am
Are those the only errors that checkDB's returning? If not, can you post the entire output of the following
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Detach/attach will not fix corruption, restarting the server will not fix corruption.
Please post in the correct forum in the future. There's a SQL 2005 corruption forum too.
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
January 28, 2009 at 8:56 pm
Hi Gila,
I ran the command and got the following outcome.
Msg 8928, Level 16, State 1, Line 1
Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 61504646152192 (type In-row data): Page (1:4413) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 342979622862848 (type In-row data). Page (1:4413) was not seen in the scan although its parent (1:1164) and previous (1:4412) refer to it. Check any previous errors.
Msg 8944, Level 16, State 16, Line 1
Table error: Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 342979622862848 (type In-row data), page (1:4413), row 6. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 89745 and 297.
Msg 8944, Level 16, State 16, Line 1
Table error: Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 342979622862848 (type In-row data), page (1:4413), row 6. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 89745 and 297.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 938486422, index ID 1, partition ID 342979622862848, alloc unit ID 342979622862848 (type In-row data). Page (1:4414) is missing a reference from previous page (1:4413). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'PBEHolidayAverages' (object ID 938486422).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'CPS_EASTMAN'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CPS_EASTMAN).
Note that i can afford to lose data on the database. It contains essential info.
Can you please advice.
Thanks
A.
January 28, 2009 at 9:05 pm
(I guess this post puts me over 1000 points - wooo!)
You've got corruption in one page at the leaf-level of the clustered index for that table. Running repair will cause that page to be deleted, losing all the data on that page. You might be able to get away with only a single row of lost data if you're able to delete the corrupt record yourself.
Do the following:
DBCC TRACEON (3604) -- just allows DBCC PAGE to print output to the console
GO
DBCC PAGE ('CPS_EASTMAN', 1, 4413, 3);
GO
See if it will give you the output down to row 6 - it may not. If so, use the key values to delete that record using "DELETE FROM...." . If not, it may only give you the output down to row 5. In that case, if you've got a monatonically increasing key, you can work out which row to delete. If not, its trickier and yo umay need to try to ASC and DESC order selects to find out which row is corrupt.
Basically though, you're going to lose at least one row of data from the table.
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 28, 2009 at 11:45 pm
amit (1/28/2009)
Note that i can afford to lose data on the database. It contains essential info.
Do you have a clean backup? One without the corruption?
If you do, then you can restore that backup, roll any log backups forward and fix the corruption without losing anything. If you don't have a clean backup, see Paul's reply above.
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
January 29, 2009 at 12:11 am
Hello,
I dnt know if the following existing.. just curious.. is there any dbcc checkdb with no_data_loss....
Thanks
A.
January 29, 2009 at 12:35 am
amit (1/29/2009)
I dnt know if the following existing.. just curious.. is there any dbcc checkdb with no_data_loss....
Yes. It's the REPAIR_REBUILD option. It only fixes things is all of the corruption is in the nonclustered indexes. If there's damage to the base tables (clustered index or heap) then repair rebuild will not fix the problems
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
January 29, 2009 at 1:17 am
In addition to what Gail said, what you're really asking is whether there's a way to fix these corruptions without losing data using DBCC - the answer is no. The only way to recover from corruptions where there is no redundant copy of the information is to restore from a backup. Running repair is always a last resort for when backups are not available (or the choice is made to accept data loss instead of protracted downtime while restoring from a very large backup, or many log backups).
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply