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

SQL Server detected a logical consistency-based I/O error Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 6:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:28 AM
Points: 4, 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>) 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.
Post #1545653
Posted Thursday, February 27, 2014 1:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 39,886, Visits: 36,233
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 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 #1545738
Posted Thursday, February 27, 2014 2:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 2:28 AM
Points: 4, 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
Post #1545764
Posted Thursday, February 27, 2014 2:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:49 PM
Points: 39,886, Visits: 36,233
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 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 #1545768
Posted Thursday, February 27, 2014 3:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 300, Visits: 3,364
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


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.
Post #1545778
Posted Thursday, February 27, 2014 9:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 61, Visits: 544
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/
Post #1545948
Posted Friday, February 28, 2014 7:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.
Post #1546327
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse