Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Database consistency problem detected on database

Author  Topic 

karrojo
Starting Member

26 Posts
Posted - 2010-03-18 : 05:18:43
Good Day!

I have been reading most of the inquiries and replies re database consistency problem. We have a new server and restore the last backup file of the database. I tried a query on the table that contains many records and this is the error msg:

A possible database consistency problem has been detected on database <dbname>. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database <dbname>.

Connection Broken
___________________________________________________________________

I have followed the instructions using the DBCC CHECKDB command and here is the result:

Table error: Object ID 365960380, index ID 0, page (4:1131264), row 118. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 17.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 365960380, index ID 1. Page (4:1131264) was not seen in the scan although its parent (4:560938) and previous (4:1131255) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 365960380, index ID 1. Page (4:1131265) is missing a reference from previous page (4:1131264). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table <tablename>(object ID 365960380).
CHECKDB found 0 allocation errors and 4 consistency errors in database <dbname>.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (pvao ).

I would greatly appreciate your replies on my inquiries so that we can be able to use the new server. By the way we are using Windows Server 2003, SQL SERver 2000 SP4 and runs on DELL PowerEdge T610. Thanks in advance.

Tobee

Kristen
Test

22859 Posts
Posted - 2010-03-18 : 06:15:50
Do you still have the original server / database?

If so I suggest you run a CHECKDB on that original database. If that is OK then make a new Backup, and restore that to the new server.

If the database was already damaged on the old server then we obvious have to go from there.

I recommend that you do NOT use "repair_allow_data_loss" until you have established the cause. For example, the database might be fine on the original machine, and the disk controller is faulty on the new machine.

Hopefully someone more knowledgeable about CHECKDB will be along in a few minutes ...
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-18 : 06:36:54
Run CheckDB on the original database. Post the entire output.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

karrojo
Starting Member

26 Posts
Posted - 2010-03-18 : 22:55:31

Thanks Kristen and Gail!

Here is the result of DBCC CheckDB i did on our database

Server: Msg 2535, Level 16, State 2, Line 1
Table Corrupt: Page (4:541163) is allocated to object ID 365960380, index ID 1, not to object ID -2079934458, index ID 38152 found in page header.
Server: Msg 8909, Level 16, State 1, Line 1
Table Corrupt: Object ID -2079934458, index ID 38152, page ID (4:541163). The PageId in the page header = (31493:-1341790970).
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 365960380, index ID 0, page (4:1131264), row 120. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 17.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 365960380, index ID 1: Page (4:541163) could not be processed. See other errors for details.
Server: Msg 8976, Level 16, State 1, Line 1
Table Corrupt: Object ID 365960380, index ID 1. Page (4:541163) not allocated or corrupt. Parent (4:1127738) and previous (4:541162) refer to it.
Server: Msg 8978, Level 16, State 1, Line 1
Table Corrupt: Object ID 365960380, index ID 1. Page (4:541164) is missing a reference from previous page (4:541163). Possible chain linkage problem.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 365960380, index ID 1: Page (4:1131264) could not be processed. See other errors for details.
Server: Msg 8976, Level 16, State 1, Line 1
Table Corrupt: Object ID 365960380, index ID 1. Page (4:1131264) not allocated or corrupt. Parent (4:560938) and previous (4:1131255) refer to it.
Server: Msg 8978, Level 16, State 1, Line 1
Table Corrupt: Object ID 365960380, index ID 1. Page (4:1131265) is missing a reference from previous page (4:1131264). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 2 consistency errors in table '(Object ID -2079934458)' (object ID -2079934458).
CHECKDB found 0 allocation errors and 7 consistency errors in table <tableName> (object ID 365960380).
CHECKDB found 0 allocation errors and 9 consistency errors in database <dbname>.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (pvao ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-19 : 05:46:33
Ok, the source database is damaged. I don't suppose you know when that started? I don't suppose that you have a clean backup (one without the corruption) of that database?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

karrojo
Starting Member

26 Posts
Posted - 2010-03-19 : 05:58:02

None :(... that's the last back-up we have... :(
Go to Top of Page

karrojo
Starting Member

26 Posts
Posted - 2010-03-19 : 09:35:59

Is there a good solution to the damaged database? can it be repaired? it will be a great help to us of your expertise on the matter. thanks so much for taking your time to attend to my inquiry.

Tobee
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-03-19 : 10:12:47
Well, given that you have a backup and could "have another go" I think that trying the repair_allow_data_loss choice is a good place to start.

I recommend you do this on the New machine, not the old one. There is a reason why this corruption occurred e.g. disk controller fault, memory fault, etc., and that fault will still exist on the old machine, until repaired, and exacerbate the situation.

You need Gail's input really, I don't know enough about this, but my concern would be working out what data has been lost so that you can consider how to recreate the "gaps". Also, if you have, say, Orders and Order Items you need to know what Order Items become orphaned and need a Parent re-adding - that's easy to report on - but what I don't know is whether you will have any side effect - e.g. Foreign Keys that are now compromised by the missing parent (Order) record.

Also consider putting steps in place to prevent this happening again. You need a DBCC CHECKDB run regularly (daily, ideally) and a fail-safe means of knowing when that check fails - reacting to a corrupted database within 24 hours is a whole pile better than some-time-after-the-fact, as I'm sure you would agree
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-19 : 10:58:20
Looks like 2 pages have been overwritten with garbage. Most likely an IO subsystem problem. Check logs for windows, raid, etc. However not likely to find anything if you don't know when it happened.

This can be repaired.
DBCC CheckDB ('Database Name') WITH REPAIR_ALLOW_DATA_LOSS
DAtabase must be in single user mode to run this.

As the option implies, this will lost data. 2 pages in the table whose name you removed from the checkDB output. There is no way, without a backup, to fix this without data loss.

This is SQL 2000, is it not? If so, before you go off and repair, could you also run DBCC CheckCatalog and post anything that it returns?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-19 : 11:02:54
quote:
Originally posted by Kristen

You need a DBCC CHECKDB run regularly (daily, ideally)


I wouldn't necessarily say daily. Some cases that just won't be possible (checkDB duration exceeds maint window and no 2nd server to offload onto).
CheckDB needs to be run often enough that, should corruption be detected, restoring from backup is a viable solution.

Hence minimum frequency of checkDB depends on backup retention strategy (bearing in mind that corruption will be included in full backups, may be included in diff backups and cannot be included in log backups*).

(*) Except in really unusual cases in bulk-logged recovery, where the DB does not have page verify = checksum enabled.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-03-19 : 12:20:31
Yes, fair points Gail, and I'm guessing too. My guess is that most people asking questions here have modest sized DBs and a DBCC CHECKDB is viable. I forget how long Tara and Russell said their CHECKDB takes - at least 12 hours I think - but I also presume that people this monstrously large DBs like that have access to skilled DBAs and Microsoft Support contracts and don't usually pitch up here!

I also think that those sort of small shops benefit from knowing very soon after the fault that the database has gone South ... restoring from backup a week later and possibly having some other problem (e.g. TLog backups are corrupted because everything was on the same RAID5 that took a hit from the disk controller) means that they still suffer data loss.

" page verify = checksum"

Pity that isn't the default for databases upgraded from SQL 2000 because I think that setting will provide much of the benefit of frequent DBCC CHECKDBs without actually having to have the maintenance window. As I think you told me, important to put CHECKSUM in the Backup command too.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-19 : 17:52:51
quote:
Originally posted by Kristen

but I also presume that people this monstrously large DBs like that have access to skilled DBAs and Microsoft Support contracts and don't usually pitch up here!


I'd like to think that, but experience has proven that wrong. I've seen 'accidental' DBAs with databases in the hundreds of GB

quote:
Pity that isn't the default for databases upgraded from SQL 2000 because I think that setting will provide much of the benefit of frequent DBCC CHECKDBs without actually having to have the maintenance window. As I think you told me, important to put CHECKSUM in the Backup command too.


That's why changing the setting on upgrade is recommended.

By itself, checksums on the page are not enough. Something has to read and check them. Only things that do that are checkDB and full backup with the checksum option. Full backup with checksum is not a replacement for regular checkDB. Had that discussion with Paul R recently. Can't remember his reasoning, will ask.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-03-20 : 06:37:57
"I've seen 'accidental' DBAs with databases in the hundreds of GB"

No DBA and no Microsoft Contract = more consultancy work for the likes of you and I, eh?

" That's why changing the setting on upgrade is recommended"

I don't think part-time-DBAs know about that though. Making it the default on RESTORE (from earlier version), or when changing Compatibility Mode UNLESS the WITH NO-SAFE-HEX option is used. Would cut down a lot of user-distress, even if breaking the "update keeps things the same" paradigm.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-20 : 12:37:37
quote:
Originally posted by Kristen

"I've seen 'accidental' DBAs with databases in the hundreds of GB"

No DBA and no Microsoft Contract = more consultancy work for the likes of you and I, eh?


Only if they're smart enough to know they need help and willing to pay for top people.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

karrojo
Starting Member

26 Posts
Posted - 2010-03-22 : 01:20:58
Good Day to all of you. i followed the instructions of Gail on the DBCC checkdb('<dbname>', repair_allow_Data_loss)
and here is the result:

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 365960380, index ID 0: Page (4:1131264) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 365960380, index ID 0, page (4:1131264), row 118. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 50 and 17.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 365960380, index ID 1. Page (4:1131264) was not seen in the scan although its parent (4:560938) and previous (4:1131255) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 365960380, index ID 1. Page (4:1131265) is missing a reference from previous page (4:1131264). Possible chain linkage problem.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (4:1131264) has been deallocated from object ID 365960380, index ID 0.
Clustered index successfully restored for object 'carlos.claims_checks' in database 'pvao'.
CHECKDB found 0 allocation errors and 4 consistency errors in table '<tablename>' (object ID 365960380).
CHECKDB fixed 0 allocation errors and 4 consistency errors in table '<tablename>' (object ID 365960380).
CHECKDB found 0 allocation errors and 4 consistency errors in database '<dbname>'.
CHECKDB fixed 0 allocation errors and 4 consistency errors in database 'pvao'.

we are now trying to run a query again if the problem will still exist. thank so much for all your help. ill be back again... :D... thanks...
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-22 : 03:40:08
The corruption's gone, but so has one page of data from the 'claims_checks' table. I hope that data's not as critical as it sounds.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

karrojo
Starting Member

26 Posts
Posted - 2010-03-22 : 05:15:29

Sirs / Madams, Thanks so much for all your help . We have not encountered the error message while running the same query. The data loss on the table was not so critical and can be augmented by processing again the transaction. Thanks again and more power to all of you! :D
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-03-22 : 08:37:32
Great. Now set up a regular integrity check so that, if it happens again, you can recover without data loss.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

karrojo
Starting Member

26 Posts
Posted - 2010-03-23 : 04:24:23

Thanks Gail! I'll keep note of your reminder. More power to SQL team!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site