Data Corruption - SQL Server Forums

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

Data Corruption

Author

Topic

gazzer 2004
Starting Member

12 Posts

Posted - 2010-01-22 : 05:22:53

Hi all, soory about this, I am no SQL expert and have come here for some advice.

We have a customer who has been seeing some data corruption issues on their server, so I ran CHECKDB and got the following results.

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128225). The PageId in the page header = (1:3128219).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128225) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128226). The PageId in the page header = (1:3128220).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128226) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128231). The PageId in the page header = (1:3128225).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128231) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128232). The PageId in the page header = (1:3128226).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128232) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128237). The PageId in the page header = (1:3128231).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128237) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128238). The PageId in the page header = (1:3128232).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128238) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128243). The PageId in the page header = (1:3128237).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128243) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128244). The PageId in the page header = (1:3128238).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128244) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128249). The PageId in the page header = (1:3128243).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128249) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128250). The PageId in the page header = (1:3128244).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128250) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128255). The PageId in the page header = (1:3128249).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128255) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128256). The PageId in the page header = (1:3128250).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128256) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128261). The PageId in the page header = (1:3128255).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128261) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128262). The PageId in the page header = (1:3128256).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128262) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128267). The PageId in the page header = (1:3128261).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128267) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128268). The PageId in the page header = (1:3128262).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128268) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128273). The PageId in the page header = (1:3128267).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128273) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128274). The PageId in the page header = (1:3128268).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128274) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128279). The PageId in the page header = (1:3128273).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128279) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128280). The PageId in the page header = (1:3128274).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128280) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128285). The PageId in the page header = (1:3128279).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128285) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128286). The PageId in the page header = (1:3128280).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128286) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128291). The PageId in the page header = (1:3128285).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128291) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128292). The PageId in the page header = (1:3128286).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128292) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128297). The PageId in the page header = (1:3128291).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128297) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128298). The PageId in the page header = (1:3128292).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128298) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128303). The PageId in the page header = (1:3128297).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128303) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1446195209, index ID 0, page ID (1:3128304). The PageId in the page header = (1:3128298).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1446195209, index ID 0: Page (1:3128304) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1449644656, index ID 0: Page (1:3464445) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1449644656, index ID 0, page (1:3464445), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 106 and 89.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1449644656, index ID 0, page ID (1:3464446). The PageId in the page header = (1:3464452).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1449644656, index ID 0: Page (1:3464446) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1449644656, index ID 0, page ID (1:3464447). The PageId in the page header = (1:3464453).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1449644656, index ID 0: Page (1:3464447) could not be processed. See other errors for details.

Could someone please explain what this all means.

cheers

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 05:30:56

Not my strong suit, but if you've got backups I would recommend restoring from them.

Hopefully someone will be along in a minute who can tell if the damage is only in indexes (in which case easily repaired) or within the data section (in which case any repair will lose data

)

Restore to a new, temporary, database so that you can CHECKDB that and ensure no errors before you commit to overwriting the live database!

If you have transaction backups then take one, final, TLog backup before you start your restore - you should then be able to restore up-to-the-minute without data loss (you'll have to lock people out thought to stop them making more changes after your backup

)

If you last FULL backup is also corrupted AND you have TLog backups, you can revert to an earlier FULL backup and restore that, then ALL subsequent TLog backups, to get to a current position. (Worth restoring the FULL backup first, and doing CHECKDB to ensure that is healthy before trying to restore all the TLog backups.

gazzer 2004
Starting Member

12 Posts

Posted - 2010-01-22 : 05:47:22

Thanks for your prompt reply.

They do have a full backup to revert back to, but i'm not sure about log, this is not really my server you see and is administered by others, I came on board when they started getting these issues :-(, and as I'm now expert etc etc.

I will hang on a bit before doing as you suggested, just in case someone else has more to add.

Cheers

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 06:16:22

"and as I'm now expert etc etc"

Ah ... promotion with no pay rise, eh?!

Just for some background then:

The TLog is made [by storing the transactions] as things are changed in the database. Assuming that the client is using FULL Recovery model, rather than SIMPLE, the TLog will be backed up too, and separately from the Full backup [of the main database]. The Tlog has to be backed up, otherwise the TLog file doesn't clear down. (In Simple mode the transactions are deleted as soon as they are committed, and thus there is no opportunity to store them in order to back them up)

The Tlog backups are independent of the main database, and thus odds are good that it doesn't get damaged at the same time as the main database file.

TLog backups are chronological - they can reinstate the database to any point in time (within a TLog backup file), or to the end of the (last restored) Tlog backup.

Full Backup, from the database, is likely to include any damage to the database itself, hence you have to go back to before the damage occurred.

Thus if you can go back to an uncorrupted Full Backup AND you have all Tlog backups since, you can restore the Full backup and then "play forward" all subsequent transactions by restoring the TLog backups one-by-one, in chronological order.

(And taking a final TLog backup before you start means you can get right up to "now" with, hopefully, no corruptions in any of the files and thus no data loss.)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-22 : 07:08:42

appears that several data pages are corrupt. this is a heap (index id = 0) so there is real corruption and not just a non-clustered index.

you can restore from backup (to a new db as Kristen suggested) and then drop and import just that table. which table is it? select object_name(1446195209); will show you. i just noticed there are actually two corrupt tables -- object_name(1449644656) also.

after dropping and copying in the tables, run a checkdb immediately.

if u get errors trying to drop the table, you'll need to do a full restore.

also, time to try to figure out why this happened. was there a power faailure? disk failure etc.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-22 : 07:12:17

quote:
Originally posted by Kristen

someone will be along in a minute who can tell if the damage is only in indexes

by the way --

index id 0 : heap
index id 1 : clustered index
index id > 1 : nonclustered index

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 08:00:40

Note; thanks Russell. Hopefully I'll never have to use that piece of knowledge though!

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 08:10:25

@Gazzer_2004 - note that restoring [just] that TABLE from an older backup is a bit fraught if it will have been updated in the meantime, in the live database. Particularly if there are foreign keys associated with it. If its just some code lookup table that never changes (lets hope

) then its a tasty solution.

In the past I have been able to export data from a damaged table using an index

SELECT *
FROM MyTable
WHERE SomeIndexedColumn < 'DamagedValue'

and

SELECT *
FROM MyTable
WHERE SomeIndexedColumn >= 'NextUnDamagedValue'

but that depends on having an index ... which might be a longshot if its a Heap Table.

P.S. If you can, stick a Clustered Index on all your tables; helps with other things too.

What SQL version is this running on?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-22 : 09:48:57

Can you post the entire output of the following please. There appear to be some lines missing from your post.


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Can you check on some things please

So, how old is the oldest backup?
Are there transaction log backups?
How critical is data in this table?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-22 : 09:50:55

quote:
Originally posted by Kristen

Hopefully someone will be along in a minute who can tell if the damage is only in indexes (in which case easily repaired) or within the data section (in which case any repair will lose data )

Damage is in the heap. This is either repairable with data loss, or not repairable at all. That's not the entire output of checkDB, so can't say 100% for sure.

--
Gail Shaw
SQL Server MVP

gazzer 2004
Starting Member

12 Posts

Posted - 2010-01-25 : 02:53:20

Hi guys, soory this is the rest of the output.

CHECKDB found 0 allocation errors and 56 consistency errors in table 'tt_mileage_extract' (object ID 1446195209).
CHECKDB found 0 allocation errors and 6 consistency errors in table 'COSTING_OUTPUT' (object ID 1449644656).
CHECKDB found 0 allocation errors and 62 consistency errors in database 'PSELIVE'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PSELIVE ).

Thanks for all the replies so far, it does give me something to think about at least.

Ill have to contact out customer to find out if a table restore is applicable, otherwise it may have to be a full restore.

Cheers

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-25 : 03:17:30

Restore from backup is definitely the better option here. A repair is going to lose quite a lot of data.

You also need to do some root cause analysis. It looks, from the checkdb output, as if an entire section of the file has almost been moved. (actual page numbers are higher than they should be). You need to do some investigation to find the cause, so that it won't happen again. Start with the IO subsystem

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources