sevire problem error Attempt to fetch logical page

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

sevire problem error Attempt to fetch logical page

Author

Topic

sachinsoni84
Starting Member

2 Posts

Posted - 2010-05-31 : 03:11:21

Attempt to fetch logical page (1:1489296) in database 8 failed. It belongs to allocation unit 72057605259132928 not to 72057630005985280.

when i am running select * from tablename its givin error

and when i ran dbcc checktable on that table givil a lot of error as shown below

Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594242203648 (type Unknown), page (1:1489334). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 46270473 and -4.
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'jpJobPostings'.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489296) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Page (1:1489296) was not seen in the scan although its parent (1:10251) and previous (1:1489271) refer to it. Check any previous errors.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489297) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 435 refers to child page (1:1489297) and previous child (1:1489296), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489298) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 436 refers to child page (1:1489298) and previous child (1:1489297), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489299) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 437 refers to child page (1:1489299) and previous child (1:1489298), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489300) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 438 refers to child page (1:1489300) and previous child (1:1489299), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489301) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 439 refers to child page (1:1489301) and previous child (1:1489300), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489302) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 440 refers to child page (1:1489302) and previous child (1:1489301), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489303) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 441 refers to child page (1:1489303) and previous child (1:1489302), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489328) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 442 refers to child page (1:1489328) and previous child (1:1489303), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489329) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 443 refers to child page (1:1489329) and previous child (1:1489328), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489330) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 444 refers to child page (1:1489330) and previous child (1:1489329), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489331) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 445 refers to child page (1:1489331) and previous child (1:1489330), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489332) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 446 refers to child page (1:1489332) and previous child (1:1489331), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489333) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 447 refers to child page (1:1489333) and previous child (1:1489332), but they were not encountered.
Msg 8928, Level 16, State 1, Line 2
Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data): Page (1:1489334) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data): Page (1:1489335) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data): Page (1:1489336) could not be processed. See other errors for details.
There are 9064 rows in 1299 pages for object "jpJobPostings".
CHECKTABLE found 0 allocation errors and 31 consistency errors in table 'jpJobPostings' (object ID 1049822852).
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1980586144, index ID 1, partition ID 72057629988290560, alloc unit ID 72057594229686272 (type LOB data), page ID (1:1489335) contains an incorrect page ID in its page header. The PageId in the page header = (1:1562438).
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1980586144, index ID 1, partition ID 72057629988290560, alloc unit ID 72057594229686272 (type LOB data), page ID (1:1489336) contains an incorrect page ID in its page header. The PageId in the page header = (1:1562439).
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'canCandidateResumes' (object ID 1980586144).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (jobs4hunt1.dbo.jpJobPostings).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

means 31 consistency error in a table and
2 consistency error in another table
donno what to do pls help to make thease table runnable again pls help

sachin

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-31 : 03:40:40

Please run the following and post the full and complete output.

DBCC CheckDB('<Database Name>') WITH No_Infomsgs, All_ErrorMsgs

Do you have a clean backup of this database? One without this corruption?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-05-31 : 04:04:39

If you have backups and FULL Recovery model:

0. Ideally set database to SINGLE USER / DBO to prevent any further user access
1. Take TAIL Tlog backup
2. Restore latest FULL and all subsequent TLog backups to a new, temporary, database
3. Run DBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS on the new database
4. If there are no errors then repeat the RESTORE the the live database
5 Drop the temporary database

If there are errors found then you may want to consider restoring the FULL backup again and testing that (i.e. without restoring TLog backups). If that is clean then work forward through TLog backups until corruption found. If that full backup is NOT error-free use an earlier FULL backup. When you find a clean FULL backup restore all TLog backups since.

Chances are good that you will be able to restore from a clean FULL backup and all TLog backups since with no data loss ...

... assuming you have backups and the database is in FULL recovery model.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-31 : 04:22:48

quote:
Originally posted by Kristen

If there are errors found then you may want to consider restoring the FULL backup again and testing that (i.e. without restoring TLog backups). If that is clean then work forward through TLog backups until corruption found.

A transaction log backup will not contain database corruption. Full and diff backups can, because they back up the actual data pages, but a transaction log backup just contains the log of changes, not the actual data pages.
Since corruption (in the vast majority of cases) is IO subsystem problems, it will not be a logged change and hence restoring a log backup will not replay corruption into the DB.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-05-31 : 05:14:38

Gail: my point was intended to be that checking WHICH Full backup is uncorrupted provides a clean starting point.

Restoring a corrupted FULL backup, and then all Tlogs since, is unlikely to make it clean

My steps were on the assumption that it was reasonable to try the most recent FULL backup + TLogs first in the hope that the corruption is recent. I don't know, of course, if the O/P has routine DBCC CHECKDB procedures in place to get early warning of corruptions, and if not then the corruption may have existed in the database for ages. But I still think that's a reasonable approach (i.e. going to the trouble of restoring the most recent Full + TLog backups in the hope of getting a clean DB). If not ... then it is necessary to work backwards to find a clean FULL backup, and then using that and all TLogs it should be possible to get the database back without data loss.

Again, more assumptions - e.g. that the database is in FULL recovery model.

But also possible that Transaction log backup is damaged by IO subsystem problems too surely?

Is it possible to test a restored FULL Backup with the DB in NORECOVERY? (Otherwise presumably you have to RESTORE Full Backup, do the CHECKDB, and then IF it is OK restore it again, plus all the TLog backups, which kinda doubles the work and elapsed time

@sachinsoni84: Separate from that Gail will be able to advise on repairing your database when you provide output from DBCC CheckDB('<Database Name>') WITH No_Infomsgs, All_ErrorMsgs

sachinsoni84
Starting Member

2 Posts

Posted - 2010-05-31 : 05:22:02

Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594242203648 (type Unknown), page (1:1489334). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 46270473 and -4.
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'jpJobPostings'.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489296) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Page (1:1489296) was not seen in the scan although its parent (1:10251) and previous (1:1489271) refer to it. Check any previous errors.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489297) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 435 refers to child page (1:1489297) and previous child (1:1489296), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489298) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 436 refers to child page (1:1489298) and previous child (1:1489297), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489299) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 437 refers to child page (1:1489299) and previous child (1:1489298), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489300) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 438 refers to child page (1:1489300) and previous child (1:1489299), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489301) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 439 refers to child page (1:1489301) and previous child (1:1489300), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489302) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 440 refers to child page (1:1489302) and previous child (1:1489301), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489303) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 441 refers to child page (1:1489303) and previous child (1:1489302), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489328) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 442 refers to child page (1:1489328) and previous child (1:1489303), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489329) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 443 refers to child page (1:1489329) and previous child (1:1489328), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489330) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 444 refers to child page (1:1489330) and previous child (1:1489329), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489331) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 445 refers to child page (1:1489331) and previous child (1:1489330), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489332) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 446 refers to child page (1:1489332) and previous child (1:1489331), but they were not encountered.
Msg 2533, Level 16, State 1, Line 2
Table error: page (1:1489333) allocated to object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 2
Table error: Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data). Index node page (1:10251), slot 447 refers to child page (1:1489333) and previous child (1:1489332), but they were not encountered.
Msg 8928, Level 16, State 1, Line 2
Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data): Page (1:1489334) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data): Page (1:1489335) could not be processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 2
Object ID 1049822852, index ID 1, partition ID 72057629992157184, alloc unit ID 72057630005985280 (type In-row data): Page (1:1489336) could not be processed. See other errors for details.
There are 9064 rows in 1299 pages for object "jpJobPostings".
CHECKTABLE found 0 allocation errors and 31 consistency errors in table 'jpJobPostings' (object ID 1049822852).
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1980586144, index ID 1, partition ID 72057629988290560, alloc unit ID 72057594229686272 (type LOB data), page ID (1:1489335) contains an incorrect page ID in its page header. The PageId in the page header = (1:1562438).
Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 1980586144, index ID 1, partition ID 72057629988290560, alloc unit ID 72057594229686272 (type LOB data), page ID (1:1489336) contains an incorrect page ID in its page header. The PageId in the page header = (1:1562439).
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'canCandidateResumes' (object ID 1980586144).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (jobs4hunt1.dbo.jpJobPostings).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sachin

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-31 : 05:31:37

Sachin, I asked you to run CheckDB, not CheckTable. Please run the command I asked you to and post the full and complete output here.

I cannot help you further until you post the full output of CheckDB.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-31 : 05:35:24

quote:
Originally posted by Kristen

Gail: my point was intended to be that checking WHICH Full backup is uncorrupted provides a clean starting point.

Restoring a corrupted FULL backup, and then all Tlogs since, is unlikely to make it clean

Obviously. I was contesting your statement "If [the restore of the full backup] is clean then work forward through TLog backups until corruption found."

If the restore of the full was clean, rolling the log backups forward is not going to restore corruption into the database.

quote:
But also possible that Transaction log backup is damaged by IO subsystem problems too surely?

Sure, but in that case the restore log will fail.

quote:
Is it possible to test a restored FULL Backup with the DB in NORECOVERY?

No. DB must be online to run checkDB.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-05-31 : 05:54:38

Ah, OK. Sorry, wasn't sure which point was ambigous!

I was thinking that:

FULL is clean, first batch of Tlog backups are clean, then a TLog backup is damaged (because of I/O subsystem error).

Is TLog restore guaranteed to detect such errors (using CRC check or somesuch)? If so that would be good / reassuring to know

"DB must be online to run checkDB"

Yup, thought so, thanks. Would be nice to be able to CHECKDB using STANDBY or somesuch, because if the FULL was OK then could proceed to restoring TLogs. I imagine on a huge database the Restore takes some time, and having to repeat it takes more time of course ....

... mind you, on a huge database no excuse for not having systems in place to restore backups to another machine and CHECKDB them there to prove they are "clean".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources