How to rebuild index on Internal Table?

Question

Post reply

How to rebuild index on Internal Table?

mandavli

SSCrazy

Points: 2122
More actions
May 11, 2021 at 9:03 pm

#3882071

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?

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

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.

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

prettsons SSCertifiable Points: 7453 More actions · Answer 3

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 🙂