How to rebuild index on Internal Table?

  • I keep on getting error :

    Unable to find index entry in index ID 1, of table 574677145, in database 'yyyyy' The indicated index is corrupt or there is a problem with the current update plan

    So I found the table , this table is internal table type 'IT' in sysobjects

    I ran DBCC CECKDB and also  DBCC CHECKTABLE

    DBCC CHECKDB ([yyyyy], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

    DBCC CHECKDB('yyyyy', REPAIR_REBUILD)

    none of them return error.

    So I got index name when try to rebuild that I get error

    could not find object 'zzzzzz' because it does not exist or you do not have permissions and I am logged in as sa.

    Can anyone help?

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Please read the second paragraph from the article at the following URL for the answer to that...

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-internal-tables-transact-sql?view=sql-server-ver15

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • TROUBLESHOOTING STEPS

    1. Install and run SQL Management Studio to access the database.

    2. Make a full backup of the database mentioned in the Description line of the error.

    3. Stop all Milestone services that use this database.

    4. Right-click the database and select Properties ? Options.

    5. Scroll down the lower right-hand frame to find Restrict Access. Set this to Single User.

    6. Click "OK." The database is now locked from any interaction from any service.

    7. Select "New Query."

    At this point you are ready to run checkdb or checktable depending on the error. In the query window, type the following for checkdb:

    DBCC CHECKDB(<name of the database>, REPAIR_REBUILD);

    Then click on the "Execute Query" button. SQL will then check and repair the database.

    If the error refers to a specific table, then checktable may be necessary. Use the following syntax for checktable:

    USE <name of the database>;

    GO

    DBCC CHECKTABLE(<name of the table>, REPAIR_REBUILD);

    Once complete, both queries will produce a report on the rows examined. After this, you need to put the database back into Multi-User mode by using the following steps:

    1. Close the query tab.

    2. Right-click the database, select Properties ? Options.

    3. Scroll down the lower-right hand frame to find Restrict Access. Set this to Multi-User.

    4. Click OK.

    5. Restart your Milestone services.

    You can also check this blog.

    SQL Database Recovery Expert 🙂

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply