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
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
seth delconte
seth delconte
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2491 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 (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221520 Visits: 46281
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1839 Visits: 1410
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
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221520 Visits: 46281
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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28724 Visits: 11495
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1839 Visits: 1410
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
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 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
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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