Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2005
    Posts
    50

    Red face Unanswered: suspect database.....



    One of my prodn databases went into suspect mode today..
    I was able to recover it.
    The tech support guyz had rebooted the server couple of times
    (out of ignorance) cos they thought if wud fix it.
    I lost the sql errpor log file which had the error details as to why db when into suspect....so my questions are..

    1. Probable reasons why db when into suspect mode.
    2. Any other way to find out about the error details.
    3. When I ran dbcc checkdb with repair_rebuild option
    for some tables it gave error

    "Parent node for page (1:13075) was not encountered"

    How can i resolve the consistency errors.
    Is DBCC CHECKTABLE sufficient.???

  2. #2
    Join Date
    Jun 2005
    Posts
    50
    is it so difficult to reply...

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    What was the complete error the DBCC CHECKDB gave you? Try running DBCC CHECKTABLE and see if that works. It won't hurt anything to try. If it doesn't work, it will tell you that it's necessary to run allowing data loss.

    Also, how did you lose the error logs? Look in the Logs directory. You should see error log files with .1, .2, etc on the end of them. These are the historical error files.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Jun 2005
    Posts
    50
    alas !..some reply..

    the results of DBCC CHECKDB is around 50 pages,...
    here is a brief snapshot..

    [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 0, index ID 0, page ID (1:13015). The PageId in the page header = (0:0).
    [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 0, index ID 0, page ID (1:13039). The PageId in the page header = (0:0).
    [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 18099105, index ID 0: Page (1:13015) could not be processed. See other errors for details.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 18099105, index ID 1. Page (1:12777) is missing a reference from previous page (1:12567). Possible chain linkage problem.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 18099105, index ID 9. Page (1:12770) is missing a reference from previous page (1:12551). Possible chain linkage problem.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 562101043, index ID 0: Page (1:13039) could not be processed. See other errors for details.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 562101043, index ID 1. Page (1:13032) is missing a reference from previous page (1:12993). Possible chain linkage problem.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 562101043, index ID 2. The high key value on page (1:1779) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:13072).


    DBCC CHECKTABLE would give out the same error even though specified with'
    the REPAIR_REBUILD option....

    it so happened that the sql server was rebooted many a times by the tech support guy as a result of which the error log file was recycled ...

    I know i can change the option from EM to retain > 6 files...
    is it possible thru QA so i can create a .bat file and apply to all my servers....

    >>
    When life gives you a lemon, fire the DBA.

    I dislike that line......

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You should be able to recover the corruption messages from the Windows Application event log. In the System event log, check for disk errors, as you may have a bad disk, controller, or even a cable.

    Judging from the apparent extent of the damage (multiple tables, including indexid 1 on some), you are very likely looking at a database restore from a backup that was taken before the corruption happened. Do you know when the corruption happened, by chance?

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    1. Fire that tech guy. He's an idiot.
    2. Fire the idiot who setup your PRODUCTION SERVER without proper cache on the controllers and without a good UPS.
    3. Pray you have an earlier backup.
    4. Call MS Support and open a ticket. It costs $250 and might save your job.
    5. Check the server for hardware problems, as they've mentioned here.

    I'm dead serious about all five. When you have this type of issue, engage everyone you can find. Call the vendor. Call Microsoft. When you get someone on the phone, tell them it's a production downtime issue and you're losing money. That blows past all the frontline support they throw up to get rid of people.
    Last edited by derrickleggett; 10-03-05 at 23:22.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Jun 2005
    Posts
    50
    I have called in the hardware guy to inspect the disk for any errors...
    I guess its more of an hardware problem cos when I ran
    DBCC INDEXDEFRAG on the table causing the problem it gave me ...

    I/O error (bad page ID) detected during read at offset 0x000000065ae000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2000\data\myDB.mdf'.

    I have restored the database and the appln is fine now...so I guess calling MS wont be neccessary ..
    but wondering how to avoid or minimize such problems...


    Regarding having UPS and cache controllers..well ...hmmmm..
    I can recommend but not insist cos m just an consultant...

    I plan to have
    DBCC CHECKTABLE with REPAIR_REBUILD
    DBCC CHECKALLOC
    DBCC INDEXDEFRAG
    DBCC UPDATEUSAGE on a daily basis as a nite job....
    any thing else to be added to ensure database consistency???

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    See if you can cobble together an estimate for the dollar amount of the data lost in the restore (time spent re-entering, lost orders, etc.). Then contrast that with the cost of the new equipment.

    EDIT: I would not do the REPAIR_REBUILD. Generally it is not necessary, unless you are seeing corruption on a daily basis. In that case, you got much bigger problems.

  9. #9
    Join Date
    Jun 2005
    Posts
    79
    The errors and page/object IDs in your error messages exactly match those of a corruption thread (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55996) on sqlteam.com - my guess is that its you on that thread too. Please see my reply on that thread.

    Don't ever have a regular job running repair - you need to work out why the problem occured and take steps to prevent it before you lose your entire database.

    Don't run CHECKALLOC and indidivual CHECKTABLEs - just run a CHECKDB, which does all of them.

    Don't run INDEXDEFRAG nightly unless you have range scans on the indexes that you can see slowing down as a result of logical fragmentation - otherwise INDEXDEFRAG will not buy you anything.

    Why do you feel the need to run UPDATEUSAGE every night? Are you seeing the page/row counts getting seriously out of whack every day?
    Paul Randal
    Dev Lead, Microsoft SQL Server Storage Engine

  10. #10
    Join Date
    Jun 2005
    Posts
    50
    The errors and page/object IDs in your error messages exactly match those of a corruption thread (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55996) on sqlteam.com - my guess is that its you on that thread too. Please see my reply on that thread.

    >> yeah m the same guy...thanks for replies....
    I would follow up in this forum...

    Don't ever have a regular job running repair - you need to work out why the problem occured and take steps to prevent it before you lose your entire database.
    >> point taken....


    Don't run CHECKALLOC and indidivual CHECKTABLEs - just run a CHECKDB, which does all of them.
    >> I read from BOL that CHECKDB is a superset ....but if thats the case then
    how is that CHECKALLOC was able to correct the problems and not CHECKDB...wat i did was
    1. ran CHECKDB..which left a few errors...later i found it corrected only
    7 of 22...

    2. Ran CHECKALLOC...no errors..

    3. Ran CHECKDB again..this time no errors...







    Don't run INDEXDEFRAG nightly unless you have range scans on the indexes that you can see slowing down as a result of logical fragmentation - otherwise INDEXDEFRAG will not buy you anything.
    >>> ok..


    Why do you feel the need to run UPDATEUSAGE every night? Are you seeing the page/row counts getting seriously out of whack every day?

    >> atleast the day when it when in suspect..but wud dig more into that...

  11. #11
    Join Date
    Jun 2005
    Posts
    50
    now that i know i have 2 tables screwed up cos of some hardware problem
    is there a way to get all data from those tables...
    I get the following error when I try to select all the records..

    "Could not continue scan with NOLOCK due to data movement."
    even though the database is in single_user mode...

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You shouldn't be using NOLOCK if the database is in Single user mode.

    Try to use BCP ... OUT and see if you manage to extract the data this way.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jun 2005
    Posts
    50
    In QA

    The last row returned by bcp is for PK sequence..87360
    whereas the max PK seq is 99959
    When I try to select records between 87360 and 99959
    no rows are returned....

    But when I try to select using
    WHERE sequence = 99959 I get the record..

    when I use bcp
    and use -F84011 (since bcp returned 84010 in the 1st try it )
    it doesnt give any records...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •