Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Table data Corrupted Expand / Collapse
Author
Message
Posted Thursday, May 17, 2012 6:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 12:28 AM
Points: 31, Visits: 156
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

Post #1301673
Posted Thursday, May 17, 2012 7:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, February 21, 2014 7:54 AM
Points: 1,619, Visits: 1,233
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
Post #1301762
Posted Thursday, May 17, 2012 7:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1301770
Posted Monday, May 21, 2012 4:32 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:10 AM
Points: 112, Visits: 1,207
The error message comes when database get corrupted. Unfortunately getting Msg823 is not a good sign because there is no repair for this error, do you have any backups? This is a severe Hardware error likely only fixable by restore.

SQL Database Recovery Expert
Post #1303266
Posted Tuesday, May 22, 2012 2:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 12:28 AM
Points: 31, Visits: 156
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
Post #1303946
Posted Tuesday, May 22, 2012 2:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 42,468, Visits: 35,537
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1303950
Posted Tuesday, May 22, 2012 7:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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.
Post #1304167
Posted Wednesday, May 23, 2012 5:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:10 AM
Points: 112, Visits: 1,207
Hello DIB IN, I have found one post related to this error please check it:
http://social.msdn.microsoft.com/Forums/eu/vbgeneral/thread/de945293-a444-4f53-9316-50ee0a5296ad
Hope it will help you to resolve this error!!


SQL Database Recovery Expert
Post #1304850
Posted Tuesday, June 5, 2012 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:40 AM
Points: 166, Visits: 267
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
Post #1311166
Posted Thursday, February 7, 2013 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 5:14 AM
Points: 4, Visits: 2
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/
Post #1416984
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse