SQLTeam.com | Weblogs | Forums

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


#1

Here is the situation. My client has a good working copy of a database called JCDLive. The unfortunate part is a administrator ran a process on the front end that caused a bunch of financial data to be cleared. The database is intact but now they want to go back to the previous week. Ok no problem, restore from a backup. I use an offsite backup service and so I went and retrieved the backup and restored the database (side not I have tried multiple restore methods from backup; Used VSS and tried replacing mdf and log files). Great so far. Database attaches fine and everything looks great until they try the front end and find out they can't logon. I look into the database now and find that I can not run a DBCC CHECKDB without getting an error.

Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Try

DBCC CHECKDB ('JCDLive') WITH TABLOCK, ALL_ERRORMSGS, NO_INFOMSGS

  • No LUCK, same error.

Check ERRORLOG in MSSQL LOG folder and see this.

2016-01-25 21:33:36.99 spid52 Error: 824, Severity: 24, State: 2.
2016-01-25 21:33:36.99 spid52 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:37703; actual 0:0). It occurred during a read of page (1:37703) in database ID 23 at offset 0x0000001268e000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JCDLive.mdf:MSSQL_DBCC23'. 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.
2016-01-25 21:33:37.02 spid52 DBCC CHECKDB (JCDLive) WITH no_infomsgs executed by sa terminated abnormally due to error state 5. Elapsed time: 0 hours 0 minutes 6 seconds.

I can go back to the live data files of course but that db has a bunch of messed up transactions. It would take a very long time to reverse what they did. Why am I having issues restoring from backup on this db? What am I doing wrong?

I have tried
DBCC CHECKDB (JCDLive, REPAIR_REBUILD)
DBCC CHECKDB (JCDLive, REPAIR_ALLOW_DATA_LOSS)

I have tried restoring even older backups from offsite backup with no luck either.


#2

how was the backup made?

  1. Is this a Backup File, generated by SQL Server, which was THEN copied to offsite / tape / whatever?

or

  1. Is this a backup copy of the MDF / LDF files?

Sounds like (2), but I just want to be sure.

Unless some special "Backup Agent" was used, or the SQL Service was STOPPED when the MDF / LDF files were copied, I would not expect the backup to be usable as the files would be changing dynamically during the backup. With Snapshop type systems it is possible to copy the MDF / LDF files, but IMHO this is usually undesirable (compared to method #1)

I presume you are "restoring" to a different, temporary, database name - or to a different Server (so that the original database is unaffected). Thus you can revert to Current Database if you need to.

I also presume that the database is in SIMPLE Recovery Model? If it is in FULL Recovery Model then presumably you also have LOG Backups, and that will offer an alternative route to getting the database back - but you will have to find a Known Good FULL Backup and then restore ALL Log Backups since then (until the time just before the accidental deletion - using the STOP AT restore option)


#3

We use Mozy Pro which uses VSS to backup the SQL databases.

When it restores it does this by copying the mdf and ldf files for the particular database. I think your right about the snapshot info. If that is the case then the backup is kind of a waste of money.

Unfortunately we have a maintenance plan in SQL Server on the other databases that does a full backup to a .bak file but for some reason the database in question didn't have this setup. Also your are correct about the database status it was set to simple.

I don't fully understand how I could restore from the VSS backup and restore the logs? Do you have a step by step on how I could try this?