November 28, 2016 at 1:03 pm
I have a SQL Server 2005 instance where DBCC CHECKDB detected error 2570 - a data purity error. The issue in may case is that there are some values that are too long for the defined data type of NUMERIC(2,0) so DBCC CHECKDB is pointing out the problem for this databases. I had been running DBCC PAGE to get the output for the bad value. This worked fine for the first two errors returned after I ran DBCC CHECKDB in SSMS specifically against the database in question. However, when I run the below, I get the error message,"(38 row(s) affected) An error occurred while executing batch. Error message is: Arithmetic Overflow." I get the page header and that's it.
DBCC TRACEON (3604);
GO
DBCC PAGE ('MyDB', 1, 2090354, 3)
The output from DBCC CHECKDB that I am trying to examine is this:
Page (1:2090354), slot 34 in object ID 1811133843, index ID 7, partition ID 72057594060275712, alloc unit ID 72057594064863232 (type "In-row data"). Column "authevents" value is out of range for data type "numeric". Update column to a legal value.
How can I find the offending value if DBCC PAGE will not output the page info?
November 29, 2016 at 7:08 pm
Page (1:2090354), slot 34 in object ID 1811133843, index ID 7, partition ID 72057594060275712...
1. Index ID #7 is a non-clustered index. It contains copies of table data (the index keys), but the original data is held in index ID #0 (if table is a heap) or ID #1 (if it has a clustered index). The best solution is to drop and recreate (or disable and then rebuild) the index. Corruption rarely hits only a single column value; just treat the entire page (if not the whole index) to be corrupt and worthy of a drop/create. Note: you must drop or disable the index before you rebuild it, or else the rebuild will attempt to use the corrupt index, and fail. The index will be unavailable during the rebuild, and queries that would use it will have to scan or use less-optimal indexes. Also, this trick only works with non-clustered indexes.
2. If you can't get the index rebuilt without taking an unwelcome outage, can you at least get the page header to display? If you can see the header, you can check the previous and next pages in the index chain to see what range is held on the corrupt page. These values display in the header as m_nextPage and m_prevPage. If you can get those values, you can DBCC PAGE those pages to see the keys in the index just before and just after the corrupt page. That will at least cut the number of rows to test down considerably.
-- use output style 0 to show only the header
DBCC PAGE ('MyDB', 1, 2090354, 0)
-Eddie
Eddie Wuerch
MCM: SQL
November 30, 2016 at 8:13 am
Eddie Wuerch (11/29/2016)
Page (1:2090354), slot 34 in object ID 1811133843, index ID 7, partition ID 72057594060275712...
1. Index ID #7 is a non-clustered index. It contains copies of table data (the index keys), but the original data is held in index ID #0 (if table is a heap) or ID #1 (if it has a clustered index). The best solution is to drop and recreate (or disable and then rebuild) the index. Corruption rarely hits only a single column value; just treat the entire page (if not the whole index) to be corrupt and worthy of a drop/create. Note: you must drop or disable the index before you rebuild it, or else the rebuild will attempt to use the corrupt index, and fail. The index will be unavailable during the rebuild, and queries that would use it will have to scan or use less-optimal indexes. Also, this trick only works with non-clustered indexes.
2. If you can't get the index rebuilt without taking an unwelcome outage, can you at least get the page header to display? If you can see the header, you can check the previous and next pages in the index chain to see what range is held on the corrupt page. These values display in the header as m_nextPage and m_prevPage. If you can get those values, you can DBCC PAGE those pages to see the keys in the index just before and just after the corrupt page. That will at least cut the number of rows to test down considerably.
-- use output style 0 to show only the headerDBCC PAGE ('MyDB', 1, 2090354, 0)
-Eddie
Thanks for the reply Eddie. I dropped and recreated all the non-clustered indexes for that table. I then re-ran DBCC CHECKDB only to have it return data out of range errors for the same Index ID 7 but different pages than before. I guess I do remember now that it can take several passes to get all the errors. It also returned three data out of range errors for the clustered PK for the same columns reported in the non-clustered index.
I may try option 2 to see if I can figure out what the bad values are. My hope is that if I can get the PK for the affected rows that the values can be updated to a valid value.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply