April 7, 2005 at 6:16 pm
Hi,
I have the reporting database. one of the report failed on the database and I try to debug the report through query analyzer and it gave me torn page detection error, I ran the dbcc checktable and found thie error:
Table error: Object ID 157295670, index ID 4, page (4:404007).
Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'IFTBREAKDOWNSSA'.
There are 78451957 rows in 917537 pages for object 'IFTBREAKDOWNSSA'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'IFTBREAKDOWNSSA' (object ID 157295670).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (stars.dbo.IFTBREAKDOWNSSA ).
I tried to repair using repair_fast and it gave me error saying that it can not be repaired without data loss, please help me I do not want the loss.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 157295670, index ID 4, page (4:404007). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 157295670, index ID 4. Page (4:404007) was not seen in the scan although its parent (4:403482) and previous (4:404006) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 157295670, index ID 4. Page (4:404008) is missing a reference from previous page (4:404007). Possible chain linkage problem.
DBCC results for 'IFTBREAKDOWNSSA'.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
There are 78451957 rows in 917537 pages for object 'IFTBREAKDOWNSSA'.
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'IFTBREAKDOWNSSA' (object ID 157295670).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (stars.dbo.IFTBREAKDOWNSSA repair_fast).
Appriciate any help.
Thanks,
Yogi
April 8, 2005 at 2:19 am
Since index ID of 4 is fortunately not the clustered index on that table, have you already tried to rebuild that nonclustered index?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 8, 2005 at 7:35 am
Hi,
I was able to repair the table successfully with repair_with_data_loss without loosing the data, but now when I ran the report it failed again with the same error of Torn Page. I ran the DBCC checkdb and found that there are more errors on the database, I am running the dbcc checkdb repair with data loss option, but I am scared of data loss.
Do I have any other options?
This report has to run on March 31 data and I Tried to restore the database on another server and I was able to successfully run the report upto March 29 data and when I restore March 30,05 data, it is failing with Server: Msg 601, Level 12, State 3, Procedure p_RptEFeeAging, Line 315
Could not continue scan with NOLOCK due to data movement. error.
Please help.
April 8, 2005 at 7:44 am
I was able to repair the table successfully with repair_with_data_loss without loosing the data, but now when I ran the report it failed again with the same error of Torn Page. I ran the DBCC checkdb and found that there are more errors on the database, I am running the dbcc checkdb repair with data loss option, but I am scared of data loss.
Sure, you haven't lost any data?
Check your hardware *very* carefully.
This report has to run on March 31 data and I Tried to restore the database on another server and I was able to successfully run the report upto March 29 data and when I restore March 30,05 data, it is failing with Server: Msg 601, Level 12, State 3, Procedure p_RptEFeeAging, Line 315
Could not continue scan with NOLOCK due to data movement. error.
Sorry, never came across this one before.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply