Invalid entries in sysindexes

Hi all,
I am running an INDEXDEFRAG on all tables in the database and am receiving
the following error:
DBCC INDEXDEFRAG (msdgen, 279500991, pmshr_iv_inventory_trans_init)
Server: Msg 7999, Level 16, State 8, Line 1
Could not find any index named 'pmshr_iv_inventory_trans_init' for table
'pmshr_iv_inventory_trans_init'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I looked in the sysindexes table and these entries exist, but from the EM if
you right click the table and choose Manage Indexes they are not present.
I am receiving this on 23 tables/indexes in the the entire DB and am
wondering if there is a way to remove the invalid entries programmatically.
Let me know and thanks for reading,
Jon BrabhamJon Brabham wrote:
> Hi all,
> I am receiving this on 23 tables/indexes in the the entire DB and am
> wondering if there is a way to remove the invalid entries programmatically
.
> Let me know and thanks for reading,
> Jon Brabham
>
Do this at your own risk...
/* Allow direct modification of system tables */
EXEC sp_configure allow, 1
RECONFIGURE WITH OVERRIDE
GO
USE database_name
GO
/* Backup sysindexes table, just in case */
SELECT * INTO sysindexes_bak FROM sysindexes
DELETE FROM sysindexes WHERE name = index_name
GO
/* Dis-allow direct modification of system tables */
EXEC sp_configure allow, 0
RECONFIGURE WITH OVERRIDE
GO
/* Recreate the dropped index */
CREATE INDEX index_name ON table_name (key definitions)|||Jon
I'd not follow Tracy's advice because it is really risky to deal with
system tables.
Run DBCC CHECKDB first and see whether it retruns errors or not.
"Jon Brabham" <jbrabham@.hsesystems.com> wrote in message
news:eZ1EEBYkGHA.408@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I am running an INDEXDEFRAG on all tables in the database and am receiving
> the following error:
> DBCC INDEXDEFRAG (msdgen, 279500991, pmshr_iv_inventory_trans_init)
> Server: Msg 7999, Level 16, State 8, Line 1
> Could not find any index named 'pmshr_iv_inventory_trans_init' for table
> 'pmshr_iv_inventory_trans_init'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I looked in the sysindexes table and these entries exist, but from the EM
> if you right click the table and choose Manage Indexes they are not
> present.
> I am receiving this on 23 tables/indexes in the the entire DB and am
> wondering if there is a way to remove the invalid entries
> programmatically.
> Let me know and thanks for reading,
> Jon Brabham
>|||Yeah I agree, good to know though.
I noticed that all of the indexes that happened to be giving me this error
had an sysindexes.indid of 0 so I just filtered them out.
If anyone knows a good way to automatically re-certify all the data in these
tables it would also be good to know.
Thanks for the replies!
Jon
ID of index:
1 = Clustered index
>1 = Nonclustered
255 = Entry for tables that have text or image data
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ox7vKvpkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> Jon
> I'd not follow Tracy's advice because it is really risky to deal with
> system tables.
> Run DBCC CHECKDB first and see whether it retruns errors or not.
>
> "Jon Brabham" <jbrabham@.hsesystems.com> wrote in message
> news:eZ1EEBYkGHA.408@.TK2MSFTNGP03.phx.gbl...
>|||Uri Dimant wrote:
> Jon
> I'd not follow Tracy's advice because it is really risky to deal with
> system tables.
>
Question was "wondering if there is a way to remove the invalid entries
programmatically", I responded with a way to do just that, qualified
with "Do this at your own risk". Wasn't recommended that he do it, I
was answering his question.