How To Repair Error In Indexes

[pbrattin]

How can I repair this problem.

I run this:
dbcc checkdb ( 'WPBCTEST', REPAIR_ALLOW_DATA_LOSS) with NO_INFOMSGS

and I get this:
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 2: Errors found in text ID 92821913600 owned by data record identified by RID = (1:134968:0) id = 1894206444 and indid = 3.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:1215), slot 1, text ID 92821913600 is referenced by page (1:134968), slot 0, but was not seen in the scan.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 2. The text, ntext, or image node at page (1:96105), slot 1, text ID 92821913600 is not referenced.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sysindexes' (object ID 2).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'WPBCTEST'.

As I read this response, I think the server is saying that text ID 92821913600 is shown to be at page (1:1215), slot 1 but it is not there. Also, another text object text ID 92821913600, same id as the other object, is at page (1:96105), slot 1 but there is no reference to it. Apparently the reference to the text object got changed somehow and that is the problem.

DBCC DBREINDEX and any of the versions of DBCC CHECKTABLE returns the same message as shown above. The system will not let me drop the index nor will it let me drop the table.

 

[tlbroadbent]

The problem may be in the sysindexes table. Try the following.

DBCC CHECKTABLE ('sysindexes', REPAIR_REBUILD)

If that doesn't work, try deleting entries from sysindexes for the WPBCTEST table.

exec sp_configure 'allow updates',1
reconfigure with override
go
delete sysindexes
where id=object_id('dbo.WPBCTEST') and indid>1
go
exec sp_configure 'allow updates',0
reconfigure with override
go
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[pbrattin]

Good idea. But it did not work. I get the same connection broken message.

I tried the following:

DBCC DBREPAIR('WPBC', repairindex, 'Employee',1)

and it said it worked. But it did not.

I cannot even get the following to work:
Select * From SysIndexes Where id=object_id('Employee')
It gives the same Connection Broken Message.

Any other ideas? There's gotta be a way to edit the data in SysIndexes and change the 1st page the 1st index for that table is pointing to. We know what it should be, right?

 

[tlbroadbent]

Did you run the repair on the sysindexes table? Did you try to delete the indexes from the sysindexes table? I'm unclear from your reply if you actually did either of those steps. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[pbrattin]

>I'm unclear from your reply if you actually did either of those steps.

>Did you run the repair on the sysindexes table?
Yes, I tried two. One, the one you specified:
DBCC CHECKTABLE ('sysindexes', REPAIR_REBUILD)
It returns the same consistency error messages as
DBCC CHECKDB

and
DBCC DBREPAIR ('WPBC',repairindex,'sysindexes',1)
It returns a message saying clustered indexes cannot be recreated on sysindexes.

>Did you try to delete the indexes from the sysindexes table?
Yes, it will not delete them. I run:
Delete sysindexes where id = object_id('employee')
and get the Connection Broken message.

Any more ideas (which are really appreciated)?