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


SQL Server detected a logical consistency-based I/O error


SQL Server detected a logical consistency-based I/O error

Author
Message
VIII
VIII
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 33
GilaMonster (2/26/2014)
I hope that database isn't very important... It's very likely there will be some data loss here, it's possible that said data loss could be the entire DB.

Find some downtime, take the database into single user mode (make sure you get the single connection) and run the following (just the following, exactly as it is with no extra options, settings or switches)

DBCC CheckDB(<Database Name>Wink WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK


Let's see if that will give anything useful in terms of the actual errors.


Nothing useful, same message

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2412; actual 0:0). It occurred during a read of page (1:2412) in database ID 5 at offset 0x000000012d8000 in file 'mydatabase.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230578 Visits: 46346
No good news then.

The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable. At this point I'd normally say 'restore from backup', but...

The DB can't be fixed, you're going to have to recreate it.
Script all objects from the damaged database. Don't forget permissions, constraints, indexes, etc. Some objects may well fail to script. Get what you can.
Export all data out. BCP out is probably a good option. Some tables will almost certainly fail. You may have to export tables in chunks, range filters on the clustered index key to avoid reading damaged pages. Get what data you can.

Assuming you can get anything at all out, use the scripts and exported data to create a new DB. This will not be quick, likely days of work.

Once you've got the new DB up, get a backup strategy in place and point out to the customer (politely), that all of this work could have been avoided if they'd had backups.

Additionally... Corruption doesn't just happen for no reason. There's likely something wrong somewhere in the IO subsystem. Do your investigations, check logs, update drivers, update firmware, etc, etc. Otherwise this could all happen again in a few months.

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


VIII
VIII
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 33
GilaMonster (2/27/2014)
No good news then.

The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable. At this point I'd normally say 'restore from backup', but...

The DB can't be fixed, you're going to have to recreate it.
Script all objects from the damaged database. Don't forget permissions, constraints, indexes, etc. Some objects may well fail to script. Get what you can.
Export all data out. BCP out is probably a good option. Some tables will almost certainly fail. You may have to export tables in chunks, range filters on the clustered index key to avoid reading damaged pages. Get what data you can.

Assuming you can get anything at all out, use the scripts and exported data to create a new DB. This will not be quick, likely days of work.

Once you've got the new DB up, get a backup strategy in place and point out to the customer (politely), that all of this work could have been avoided if they'd had backups.

Additionally... Corruption doesn't just happen for no reason. There's likely something wrong somewhere in the IO subsystem. Do your investigations, check logs, update drivers, update firmware, etc, etc. Otherwise this could all happen again in a few months.


thanks, but I think the problem is worse than that

when I try to generate script, nothing to get

when I try to export data, same message 'SQL Server detected a logical consistency-based I/O error' again

when I expand folder table and store procedure, all gone

I must find another way to export data, please let me known if you have any idea

Is this solution worth to try ?

kenneth.mofokeng (2/26/2014)

Since you don't have backup , try to repair the database,

TRY

ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER



CAUTION : There is a high chance of data loss

If you mange to get database online, make sure you implement database backup strategy

GilaMonster
GilaMonster
SSC Guru
SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

Group: General Forum Members
Points: 230578 Visits: 46346
VIII (2/27/2014)
thanks, but I think the problem is worse than that

when I try to generate script, nothing to get

when I try to export data, same message 'SQL Server detected a logical consistency-based I/O error' again

when I expand folder table and store procedure, all gone


Then the DB is a complete loss. That several month old backup may be your only option. Good luck.

Is this solution worth to try ?

kenneth.mofokeng (2/26/2014)

Since you don't have backup , try to repair the database


See my reply to kenneth above. Also, as I said to you

The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable.


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


andrew gothard
andrew gothard
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2816 Visits: 5971
dwain.c (2/26/2014)
andrew gothard (2/26/2014)
dwain.c (2/26/2014)
kenneth.mofokeng (2/26/2014)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago


Since you don't have backup , try to repair the database,

TRY

ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER



CAUTION : There is a high chance of data loss

If you mange to get database online, make sure you implement database backup strategy


Odd. I'd always heard that what's important is to have a FREQUENTLY TESTED recovery strategy.


FTFY :-D


I stand corrected sir!


I shall now correct myself and add "Documented". That's a biggie too. The best strategy in the world is no use if no-one knows what it is.

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
dbalmf
dbalmf
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 699
I came across this article a while back. It might help

http://www.sqlskills.com/blogs/paul/example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-io-errors/

Ta

David

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5357 Visits: 15346
GilaMonster (2/26/2014)
kenneth.mofokeng (2/26/2014)
I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago


Since you don't have backup , try to repair the database,

TRY

ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER



NO! NO! NO!
Recommending repairing with no idea of what's actually wrong is insane! We don't even know what the errors are, a sensible recommendation cannot be made on no data. When dealing with corrupt databases the extent of the corruption is essential to know before any useful recommendations can be made. To be honest, since CheckDB's throwing errors I suspect repair will not be a option, it'll likely fail as well.

DO NOT attempt a repair at this point.


I'm most amused at this as I've recently finished part listening, part watching this video of you on the SQLPass channel on youtube :-)

http://www.youtube.com/watch?v=SQ5Pw1jeWdY

I already knew not to have this knee jerk reaction, its just the timing of it.
Brain2000
Brain2000
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 32
I know it's been a few months, so you have probably fixed this one way or another.

I have the situation where I have to keep databases online. Data integrity is second (non-financial), so corrupted records can be compared and fixed when it is convenient. Therefore, I use something called "EMERGENCY" mode.

I would recommend making a copy of the MDF/LDF first, as this technique can cause data loss. In a pinch, it has served me well many times, and I have yet to lose anything too valuable. This is also useful when you can't get a database to even go into single user mode to attempt repairs, or when you can't run backups because it just throws torn page errors.


ALTER DATABASE corrupted_db SET EMERGENCY
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE corrupted_db SET MULTI_USER


mxy
mxy
SSC Eights!
SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)

Group: General Forum Members
Points: 890 Visits: 3778
I have same errors recently, storage team didn't attach storage properly which caused the problem(Don't know why they are playing with storage in PROD using ISICI).

after the server reboot it is back to normal.
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