SYSINDEXES Index fail, error 644

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

SYSINDEXES Index fail, error 644

Author

Topic

JohnSharp
Starting Member

4 Posts

Posted - 2006-01-16 : 05:04:23

A few days ago a sproc stopped working, only noticed it this morning, when checking information to see what's up I get this:

Server: Msg 644, Level 21, State 1, Procedure uspV2InventoryFetch, Line 83
Could not find the index entry for RID '45574f44523738313834202020202020202020202020202020202020202020' in index page (1:11690152), index ID 2, database 'ASOS'.

Connection Broken

I've treid DBCC CHECKDBing all related tables and everything else. Even tried dbreindexing a couple of them too, everything reports as correct. When I looked further at the problem I spotted "index ID 2" in there, on checking this out in sysindexes it seems that the name field of sysindexes where id = 2 is SYSINDEXES and TSYSINDEXES.

I can't seem to do a DBB DBRECINDEX against a system table.

Any help appreciated.

John

JohnSharp
Starting Member

4 Posts

Posted - 2006-01-16 : 05:35:30

Well, I've been a muppet, further reading indicates that the problem is begin reported from SYSINDEXES, not actually as a problem with that table, which is nice and also confuses me even more.

Any help is now greatly appreciated... please?

John

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-01-16 : 12:04:33

quote:
Originally posted by JohnSharp

Well, I've been a muppet, further reading indicates that the problem is begin reported from SYSINDEXES, not actually as a problem with that table, which is nice and also confuses me even more.

Any help is now greatly appreciated... please?

John

Can you post the output of

DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS, ALL_ERRORMSGS

in the ASOS database?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

JohnSharp
Starting Member

4 Posts

Posted - 2006-01-17 : 08:50:02

Paul,

Cheers for getting back to me and I would had I still had it, bit of a critcal database and I didn't have a chance to keep it. A couple of useful observations though that I picked up while repairing the tables in question, I couldn't do a "select * " on it, it kept crashing and terminating the connection however if I selected any record in the table it worked. So, a quick cursor to nip through the table, export the rows to a second table, drop the offending table, reboot the db and then copy "select * into oldtable from newtable" and voila. New uncorrupted table.

Just did the line you asked for and it's returning the fact that there's no problems at all.

Bizarre.

However, must say that without your post on here Paul I'd not have gotten it repaired last night. Cheers.

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources