May 17, 2012 at 6:03 am
While Fetching record from a table we are getting below error for few records.
Error:-Msg 823, Level 24, State 2, Line 1
The operating system returned error 23(Data error (cyclic redundancy check).) to SQL Server during a read at offset 0x000000344c000 in file 'D:\DB_NAME\DB_NAME.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
After Executing DBCC CHECKTABLE ("TABLE_NAME") We are getting below error:-
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:117) with latch type SH. 23(Data error (cyclic redundancy check).) failed.
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'TABLE_NAME'.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:117) allocated to object ID 1606, index ID 0, partition ID 7205, alloc unit ID 72057 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
There are 932 rows in 927 pages for object "TABLE_NAME".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'TABLE_NAME' (object ID 1606).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DB_NAME.dbo.TABLE_NAME).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Please advice how we could rectify this error.
Regards
May 17, 2012 at 7:42 am
Do you have a backup? Have you tried the REPIAR_ALLOW_DATA_LOSS option with DBCC CHECKTABLE? If you have a current backup, I would restore the database to a NEW db, then run DBCC CHECKTABLE with REPIAR_ALLOW_DATA_LOSS on the corrupted table, and compare the old and new tables to see if anything is missing. Chances are that DBCC CHECKTABLE with REPIAR_ALLOW_DATA_LOSS will fix the issue without causing any data loss.
_________________________________
seth delconte
http://sqlkeys.com
May 17, 2012 at 7:47 am
Please DO NOT run CheckDB with any repair option at this point.
Please run the following and post the full and complete output.
DBCC ChecKDB('<database name>') WITH NO_INFOMSGS, ALL_ERRORMSGS
What backups do you have available?
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
May 21, 2012 at 4:32 am
This was removed by the editor as SPAM
May 22, 2012 at 2:09 am
DBCC ChecKDB('DB1') WITH NO_INFOMSGS, ALL_ERRORMSGS
errors:-
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 553769030, index ID 1, partition ID 72057595610529792, alloc unit ID 72057594055688192 (type LOB data). The off-row data node at page (1:87637), slot 19, text ID 71636287488 is not referenced.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:113515) allocated to object ID 553769030, index ID 1, partition ID 72057595610529792, alloc unit ID 72057595737145344 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 553769030, index ID 1, partition ID 72057595610529792, alloc unit ID 72057595737145344 (type In-row data). Page (1:113515) was not seen in the scan although its parent (1:110561) and previous (1:113514) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 553769030, index ID 1, partition ID 72057595610529792, alloc unit ID 72057595737145344 (type In-row data). Page (1:113516) is missing a reference from previous page (1:113515). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 276 consistency errors in table 'table1' (object ID 553769030).
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:117158) allocated to object ID 1606296782, index ID 0, partition ID 72057594863091712, alloc unit ID 72057594960216064 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'Table2' (object ID 1606296782).
CHECKDB found 0 allocation errors and 282 consistency errors in database 'DB1'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB1).
I think Restoration is the only option. Although this is a Test DB for which we had back up also.
Reagrds
May 22, 2012 at 2:13 am
That's not the full output. If you want an accurate assessment of the damage and your options, please post the full and unedited output of the checkDB command (or save it to a text file and attach the file to your post if it's a lot of messages)
You can run repair, but you'll lose data (error 823 is repairable). Can't tell how much without all the errors, but probably a lot
Best bet for recovering with no data loss will be to restore a backup.
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
May 22, 2012 at 7:44 am
How to troubleshoot a Msg 823 error in SQL Server
http://support.microsoft.com/kb/2015755
To start, SQL Server 2008+ contains a system table called SUSPECT_PAGES that maintains information about suspect pages and their error states. This will help scope the problem and perhaps provide some history about other errors that led up to this event.
http://msdn.microsoft.com/en-us/library/ms191301.aspx
http://msdn.microsoft.com/en-us/library/ms174425.aspx
You'll also want to run a consistency check on the disk storage system containing your database files. Rather than just data file corruption, your disk hardware in general may be failing.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 23, 2012 at 5:28 am
This was removed by the editor as SPAM
June 5, 2012 at 7:33 am
On 823 we lost around 12% using data loss option, we could not go for backup since the they were configured directly to tapes which would have taken more time to restore for 600GB or more database and backup tool Legato customer used was not that reliable.
So we checked the table rows before and after , estmated aorund 29K rows lost and luckily data stored on the table was not critical.
Good reilable backup's need to be in place to recover form errors on clustered index table's, yes CRC errors some times mey be hardware related which can determined by SYSTEM's logs.
Cheer Satish 🙂
February 7, 2013 at 5:15 am
To try the third party Software like RecoveryFix for SQL Database. You can recover MDF files and varied components stored in corrupted MDF files like tables, triggers, views, user defined data types etc. This software is favorable with all version of SQL Server. http://www.repairsqlserver.net/
April 15, 2013 at 2:59 am
if table gets corrupt..then e can recover it form cold backup...
if it had taken previously..
if our data gets corrupt the analyze which file is corrupted out of Control,Redo,Data file
then use recovery scenario for particular file.
more details visit
September 5, 2014 at 12:15 am
I think you should review the suspect_pages table in msdb to check if other pages in the same database or different databases are encountering this problem.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply