SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table data Corrupted


Table data Corrupted

Author
Message
DIB IN
DIB IN
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 158
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
seth delconte
seth delconte
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4073 Visits: 1360
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)

Group: General Forum Members
Points: 548247 Visits: 47739
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

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


prettsons
prettsons
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4425 Visits: 1475
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 :-)
DIB IN
DIB IN
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 158
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)

Group: General Forum Members
Points: 548247 Visits: 47739
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

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


Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62346 Visits: 12771
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
prettsons
prettsons
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4425 Visits: 1475
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 :-)
Satish Nagaraja
Satish Nagaraja
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 308
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 :-)
jonmichel72
jonmichel72
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search