SQL Server consistency errors in database

We have had an issue with our RMS_Headquarters(front end application to access database) database running on SQL Express 2005.

After checking all the event log on server, I came across few errors related to hard disk and sqlserver.

disk error snapshot

sqlexpress error

I have executed two commands as follows to find out any error in disk or on sql database.

chkdsk : System found bad sectors on hard disk I ran this code without /f to just get the information.

DBCC CHECKDB with ALL_ERRORMSGS, NO_INFOMSGS and the outcome was as below.

I ran chkdsk /f to resolve the disk error. This command helped to get hq database up and running (application was working fine from this point). I also ran DBCC CHECKDB with ALL_ERRORMSGS, NO_INFOMSGS on sql server to check database status. DBCC executed with few errors, please see below.

Msg 8928, Level 16, State 1, Line 1
Object ID 1429580131, index ID 1, partition ID 72057598907908096, alloc unit ID 72057598960730112 (type In-row data): Page (1:235150) could not be processed.  See other errors for details.
Msg 8941, Level 16, State 2, Line 1
Table error: Object ID 1429580131, index ID 1, partition ID 72057598907908096, alloc unit ID 72057598960730112 (type In-row data), page (1:235150). Test (sorted [i].offset <= m_freeData) 
failed. Slot 0, offset 0xffff is invalid.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1429580131, index ID 1, partition ID 72057598907908096, alloc unit ID 72057598960730112 (type In-row data). Page (1:235150) was not seen in the scan although its 
parent (1:306430) and previous (1:235149) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1429580131, index ID 1, partition ID 72057598907908096, alloc unit ID 72057598960730112 (type In-row data). Page (1:235151) is missing a reference from previous page 
(1:235150). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'TenderEntry' (object ID 1429580131).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'RMS_Headquarters'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (RMS_Headquarters).

System Information:

  • OS: Windows Server 2003 R2
  • SQL Server: SQL Server 2005 (9.0.4035)

I have a backup from Sunday(20th April 2015) evening, that backup doesnt report any error.

Please advise me what should be done in this matter: (a) restore the database from a backup dated 20th April 2015 (what data from 21st onwards will be lost) (b) continue with this database and ignore the tenderentry table errors

Your help will be appreciated.

Regards

Pratik

April 22nd, 2015 11:52am

Take a backup of current database. Restore it on test server try to select all row if it return fine, then run rebuild index on table TenderEntry(CI). Then run dbcc again to see if any error reported. If this works fine you can perform same steps on prod.

If not you can export table data to some other table or txt file then run with repair_allow_data_loss and see if this has less loss the using backup file or import data which you exported earlier.



  • Edited by dave_gona 14 hours 20 minutes ago
Free Windows Admin Tool Kit Click here and download it now
April 22nd, 2015 12:08pm

You can try & restore the back-up (The one you have taken earlier) on some other server and apply the transaction log (If you do have them) and execute DBCC CHECKDB to see if error exists or not, in case they do exist then:

  • Rebuild the clustered index for table TenderEntry and issue DBCC CHECKDB again, if error still exists then
  • Issue DBCC CHECKDB WITH REPAIR_REBUILD and execute DBCC CHECKDB again, if error still exists then
  • issue DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS and execute DBCC CHECKDB again
April 22nd, 2015 12:24pm

since the DBCC error message says - minimum - restore is "repair allow data loss". you may be little out of luck.

you can try repairing with allow data loss , (you could lose some data, do not know what data) and  then validate all the constraints on the database and then run checkdb to make sure there no issues 

or 

since you validated, you can restore yesterday backup and any logs backups up to the point. this way you atleast know what data you have.

also, you can do dbcc checkdb in a transaction, so, you can roll back the transaction, if you need to..do not know if sql 2005 has this feature.  if you are okay with restoring the database from abckup, i would advice that route, since you know the data you lost for a fact and are starting with a clean slate.

i do not think the error exists in log  backup. you can try restoring log backups  one by one and run dbcc checkdb by setting the database to readonly to make sure it is corruption free.

backing up the database is also a good idea, before you do anything.

remember, one of the constraints is also to be able bring the database online as quickly as possible, so, your business is not effected. if you think, this database does not effective your business directly, you can play around trying to do dbcc and see what it comes up

Free Windows Admin Tool Kit Click here and download it now
April 22nd, 2015 10:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics