May 19, 2009 at 12:17 am
Dear All,
If I run
DBCC PAGE (postnet, 1,629, 3);
Error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I want to discard the 629 page can it be possible and after it my database will be OK.
Thanx
Thanks
May 19, 2009 at 1:09 am
Hello,
Do you have a valid backup for the DB? (Is it a Production or Dev/Test DB?)
I ask because it is generally recommended to repair errors by restoring from backup rather than, for example, running a DBCC CheckDb with one of the Repair options.
It might also be worthwhile checking the Hard Disks on which the DB is physically stored, in case there is an underlying hardware error.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 19, 2009 at 3:09 am
I have restores many times with new backups yet I am getting the same error always.
Thanks
May 19, 2009 at 5:43 am
Please run the following and post the full results.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2009 at 11:54 pm
Dear Gail,
Thnx
out put is:
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data): Page (1:629) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data), page (1:629). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -1.
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data): Page (1:632) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data), page (1:632). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -1.
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data): Page (1:636) could not be processed. See other errors for details.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data), page (1:636), row 15. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 130.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data), page (1:636), row 15. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 130.
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data): Page (1:642) could not be processed. See other errors for details.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data), page (1:642), row 22. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 122.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data), page (1:642), row 22. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 122.
CHECKDB found 0 allocation errors and 10 consistency errors in table 'postnet.TempLan_Details' (object ID 293576084).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'postnet'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (postnet).
Thanks
May 20, 2009 at 12:45 am
Ok, you've got 4 pages that have been trashed somehow. Since you do not have a clean backup, you're going to have to run repair. Note that REPAIR_ALLOW_DATA_LOSS, as it's name implies, will lose data. In your case, 4 pages of rows from the TempLan_Details table.
Look up CheckDB in BoL and familiarise yourself with the syntax. You will need to put the DB into single user mode for the repair, so it will be offline for the duration of the repair.
The repair will not check constraints or relationships, so you may end up with orphaned records afterwards.
Make sure you do some disk diagnostics afterwards to see if you can ID the root cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2009 at 1:06 am
When I have run DBCC checkdb (postnet,repair_allow_data_loss) it gives in red color...
I want to know will it also delete the data which was existing on the deleted page...
DBCC results for 'postnet.TempLan_Details'.
Repair: The page (1:629) has been deallocated from object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data).
Repair: The page (1:632) has been deallocated from object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data).
Repair: The page (1:636) has been deallocated from object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data).
Repair: The page (1:642) has been deallocated from object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data).
Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data): Page (1:629) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 0, partition ID 19239802241024, alloc unit ID 19239802241024 (type In-row data), page (1:629). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -1.
The error has been repaired.
Thanx
Thanks
May 20, 2009 at 1:23 am
guptaajay1985 (5/20/2009)
When I have run DBCC checkdb (postnet,repair_allow_data_loss) it gives in red color...
Good. It looks like everything's repaired. Run checkDB again (without any repair option, just as I gave it in my first post here) to make sure everything's fixed.
I want to know will it also delete the data which was existing on the deleted page...
Yes it did. All the data that was on the 4 pages that were deallocated has been lost.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2009 at 2:39 am
Now No errror is coming...Will I ever get that deleted data..
Thanx
Thanks
May 20, 2009 at 3:32 am
guptaajay1985 (5/20/2009)
Will I ever get that deleted data..
No. The data that was on the pages that checkDB deallocated is gone, permanently.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2009 at 5:45 am
Thanx Gila
Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply