SQL Server Performance

Could not find row in sysindexes for database

Discussion in 'General DBA Questions' started by sqlizer, May 4, 2006.

  1. sqlizer New Member

    Hi All,

    i don't know, why my table corrupt ?
    i'm try 'select * table' and result is:
    Server: Msg 602, Level 21, State 16, Line 1
    Could not find row in sysindexes for database ID 7, object ID 1701581100, index ID -1. Run DBCC CHECKTABLE on sysindexes.

    Connection Broken

    but, other table is ok.

    Please help me. [:I]
  2. dineshasanka Moderator

    Is your table tempory table
    if sohttp://support.microsoft.com/kb/269282/EN-US/ says

    Change the non-clustered index that you created on the temporary table to a clustered index.
    -or-
    Do not use the non-clustered index on a temporary table.

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  3. satya Moderator

    ... also check whether any issues with the server hardware or the disk where this table-filegroup is located.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. sqlizer New Member

    First, the table not tempory table.

    i'm try use :
    DBCC CHECKDB('my_database') WITH ALL_ERRORMSGS

    result:
    Server: Msg 8966, Level 16, State 1, Line 1
    Could not read and latch page (1:3085) with latch type SH. sysindexes failed.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    *if try another database no problem.

    1. Database or table is broken ?
    2. how to check 'table' information on database, corrupt or good condition.

  5. satya Moderator

    Refer to KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;274310 for more information and definetly seems to be a hardware problem.
    Refer to books online about error 8966 and follow the actions prescribed http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_54ky.asp)

    ENsure you have good source of backup in this case, try to restore the backup on a different server to import the rows to a different table and then recrete this table for further DBCC checks.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. Luis Martin Moderator

    After ensure a good backup you can try:
    DBCC Checkdb with option REPAIR_ALLOW_DATA_LOSS


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  7. sqlizer New Member

    Im try emergency step:
    1. restore database [ok]
    2. create new table 'my_table_corrup_2' (destination table some with my_table_corrupt) [ok]
    3. from DTS, im transfer my_table_fix to my_table_corrup_2 [ok]
    4. yeah, My Table is fix!! [ok]
    4. but, im try drop my_table_corrupt : 'drop table my_table_corrupt'

    result:
    Server: Msg 602, Level 21, State 16, Line 1
    Could not find row in sysindexes for database ID 7, object ID 1701581100, index ID -1. Run DBCC CHECKTABLE on sysindexes.
    Connection Broken

    1.how to drop 'my_table_corrupt' ?? is permanent can't drop table on database ?
    2. what a must create new database the next, so that no corrupt one again

    let me know
  8. satya Moderator

    BTW have you executed DBCC CHECKTABLE(SYSINDEXES) for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Alvar New Member

    Hi!
    I have the same problem and I have tried the following:
    Adding a row to sysindexes manually. As I have SQL2000, it is allowed. I copied the binary fields from another row which contained the information on similar index on another table. Of course I changed the IDs and descriptions. Well, did not work, actually nothing changed. What I can not understand why SQL demands it to be with IndID -1 as it is not the usual number in that field. Changing that had no effect though.......
    DBCC CHECKTABLE(SYSINDEXES) had no effect, it sayd everything OK. I understand that the table itself is OK and only relations to other tables like sysobjects etc are not OK.
    Now what else - is there a possibility to delete the table somehow? I do not even need the damn table, just need to get rid of it.......
    Thanks in advance!
  10. satya Moderator

    Have you checked the other articles & references above, don't think running UPDATEUSAGE itself correc the issue.
    Moreover its a system table and you will not be able to drop it or ignore it. By any means this sounds like a hardware issues.
  11. Alvar New Member

    Well, I am not experienced but also not novice :)
    So I have checked all the articles from the web (first 40 that came up in Google actually) and I really did not find anything solid. Just attaching and detaching DB, but the previous time I had similar problem I even could not re-attach it. This forum just seemed to be with most quality so I tried my luck here.
    I dont want to drop the sysindexes table but the table that has the referencing row missing in sysindexes. The solution here was to export the data to another table and then delete the old one and rename the new. And the problem with that solution is that You can not drop the corrupted table, at least with usual ways.
    Yes, it could be HW problems, but I have to solve this problem first. There are many other DBs on the same server and drive and nothing has happened during 5 years. Now with this DB already second time.........
  12. satya Moderator

    Well said, that gives your indepth understanding of the issue too. [:)].
    The main problem here is hardware aka bad page in sysindexes - actually a bad page on the disk, fact that SQL Server can't repair this page so you have no choice but to restore from backup or extract the data into a new db on different hardware. So other than this I don't have any further solution to offer, as we do get such issues within our environment and keeping them to move to better hardware slowly.
  13. Alvar New Member

    Thank You!
    Well I have given up making my dreams come true and I will continue with these proven ideas[:)]
    1. Restoring from backup. Well restoring from the last backup had no effect, the error remained. And as I said I can not use a problem-free backup as it is too old. But now it gets strange - I restored it also under SQL2005 server and checkdb did not give me that error anymore!!! What could be the reason for that and could it help me somehow (backing up from SQL2005 and restoring again under SQL2000)?
    2. Extracting the data. Is that only advice that I should use new hardware for extraction or requirement? Dont get me wrong, I fully understand that something is wrong with the old HW. And what method should be used for extraction - OLE DB to OLE DB or using some kind of middle format like txt or smth? I have lots of rules, udf-s etc in that DB that will not be exported to txt for example.
    Thank You again for Your advice.
  14. rohit2900 Member

    Hello All,
    I had to move the physical mdf & ldf file for one of our database to some other drive and in order to do that I first deattached the database and then moved the physical files to the destination drive and now when I'm trying to attached the database I'm getting the below error.
    Error 602: Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes..
    I've taken the screen shots and below is the link.
    http://picasaweb.google.com/rohit2900/SQLServer
    and same error has been logged in the sql server error log. Can anybody help me in this.
    Rohit
  15. satya Moderator

    Have you checked consistency on those databases before detaching?
    Are you attaching between same versions of SQL?
    SQL Server is finding a problem with the db when trying to attach so it wants you to run DBCC against that db and you can't because it isn't attached. You can try attaching manually with sp_attach_single_file_db or if you have a backup of the db, restore from backup.
  16. rohit2900 Member

    How should I check the connsistency of the db before detaching?
    Yes boht the versions are same Infact same sql server.
    When I'm trying slp_attach_single_file_db I'm getting the belwo error.
    Server: Msg 602, Level 21, State 50, Line 1
    Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
    Connection Broken
    Rohit
  17. satya Moderator

    DBCC CHECKDB

Share This Page