Restore could not start DB.
-
GilaMonster (12/28/2008)
ALZDBA (12/28/2008)
Does this point to a table that contains text or image columns ?Try to export the data without these column, so you only lose the text/image stuff if these are broken.
Unfortunately, it's not the image/text pages that are damaged. The checkDB output points to 4 pages of index 0 (the heap) that are the ones with problems. If it were the text/image pages, it would be index 255.
Does that also go when text in row Is Set to ON ?
The important lines of the checkdb output are the 4 below.
Table error: Object ID 293576084, index ID 0, page (1:629). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Table error: Object ID 293576084, index ID 0, page (1:632). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Table error: Object ID 293576084, index ID 0, page (1:636), row 15. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 130.
Table error: Object ID 293576084, index ID 0, page (1:642), row 22. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 122.
From what I can tell from those errors, the options here are to restore a clean backup (if there is one) or run checkdb with the repair allow data loss option. Repair will discard the 4 pages listed above. Without more info of the table's structure, there's no way to say how many rows will be discarded by the repair.
Indeed, I see.
DBCC results for 'postnet.TempLan_Details'.
Server: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 293576084, index ID 0, page (1:632). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 0: Page (1:636) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 0, page (1:636), row 15. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 130.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 293576084, index ID 0: Page (1:642) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 293576084, index ID 0, page (1:642), row 22. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 46 and 122.
There are 416 rows in 12 pages for object 'postnet.TempLan_Details'.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'postnet.TempLan_Details' (object ID 293576084).
just by interpertion of the name "TempLan_Details"... can't you just drop and recreate the Temp.... table ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I think you shoulddo what Gail already suggested.
-
ALZDBA (12/28/2008)
GilaMonster (12/28/2008)
Unfortunately, it's not the image/text pages that are damaged. The checkDB output points to 4 pages of index 0 (the heap) that are the ones with problems. If it were the text/image pages, it would be index 255.Does that also go when text in row Is Set to ON ?
No. If text in row is on and the entire text/image column fits on the data page, then there won't be an index 255. 255 is for the off-row text/ntext/image/max data types, which is what I was referring to above.
If one or more of the text/image pages (index id 255) are corrupt, but the data pages (index id 0/1) are undamaged, then selecting all the columns except the text will work. If the data pages are damaged, regardless of what column types are on them, selecting specific columns only won't help, as the page and row have to be read before the columns can be selected.
At this point, I think we need a reply from the OP as to how critical that table is before a recommendation can be made.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thx sqlservercentral members for the replies:
1.I have not a clean (uncorrupted) backup.
2.Yes, If I lost a bunch of rows from that table,it would be a disaster.
3.No doesn't contains text or image columns.
I think I should do export and import for taking the table safely,but what about stored procedure and functions. Can I take that also by import and Export?
Thanks
-
Yes you can generate scripts for all the stored procedures and function through generate script option.
I think if you are able to export the data from this table (which I don't think is possible) then drop and recreate it.
-
Thu Very much....
Thanks
-
guptaajay1985 (12/28/2008)
Thx sqlservercentral members for the replies:1.I have not a clean (uncorrupted) backup.
2.Yes, If I lost a bunch of rows from that table,it would be a disaster.
I hate to be the bearer of bad news, but with no backup and those corruption errors, you are going to lose data from that table. There are two pages that are unreadable (damaged page headers) and two more that have records that are unreadable (incorrect row offsets). A straight export of all rows will run into those and cause an error (probably 823, since you're on SQL 2000)
You can try and export as much as possible by filtering and trial and error. Since that's a heap, not a clustered index, it's going to be challenging to figure out what rows are on the damaged pages.
If you run repair, it will probably discard all four pages.
You can look at the contents of those pages if you wish. There's an undocumented but reasonably well known command called DBCC PAGE that will show you the contents of a database page. See here for details - https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
Since the pages are damaged, you'll have to dump them raw (option 2) - as a straight 8k of data, though I won't guarantee that even that will work on the pages with the damaged header.
Since the data's so important, you may want to give Microsoft's customer support people a call. I think the fee is $250. I don't know if they'll have any other options or solutions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thx Gila But the problem is now taking care by my seniors so I can just give him suggestions which I m doing...
You r truly a SQL server Lover...
Thanks
-
Hello,
I would really like to know the outcome of this in case any of us get the same problem in the future, plus I would just like to know how you got on with it.
Kind Regards,
D.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply