Results 1 to 12 of 12

Thread: SQL Indexing out of sync possibly?

Threaded View

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2016
    Posts
    40

    SQL Indexing out of sync possibly?

    I will try to explain this as best I can. I apologize for the lengthy post but I am trying to be clear.

    My one table in my MSSQL DB has Indexing messed up, or so it seems.

    I have ran into this issue within the last few weeks and not sure what is causing this.

    I currently have 816 INDEXED lines within my LOAD_INFO_TABLE, MS SQL database.

    FURTHER detail to clarify....

    My concern is within the last week when I want to add a new record I immediately receive an error that there is no row at position xxx (currently the last record is at index 816). If I bypass out of that error message box and create the record anyways the record saves fine and takes the next available, and proper, index number... thereby the next record would then be index 817.

    I verified this by deleting a few of the last records, and recreated them. Every time I go past the notification box, the record writes fine and indexes to the next available index, however the message box consistently comes back on every ADD NEW request.

    I thought maybe something was amiss so I deleted a few of the latest records, only to find no matter what I do the messagebox comes back every time I try to add a new record, regardless of index position, telling me there is no record at index xxx and is always the index PRIOR to the last record, like it is two indexes behind all the time. (ie since I currently have 816 indexes, the message box notes there is no row at index 814).

    The side effect to this issue is I need to import expenses, via a second form within the program... this also has ceased recently. When I try to import expenses and save the data to the proper record I now receive the error "An item with the same key has already been added." Debug shows it is looking at index 814 (which does exist and obviously cannot write to that index number).

    Very few changes to the forms were made and nothing related to "updates" or writes have been made to the program. Cosmetic corrections to the Primary form ONLY and some updating SQL code inside the form to avoid SQL Injection was the only thing changed. The biggest change, if it truly is a change, was the program was previously compiled using VS 2015 and now is being compiled using VS 2017.

    The expense information is written to a separate database. Once that data is saved to the EXPENSE_TABLE an import is done to the LOAD_INFO_TABLE from the EXPENSE TABLE where applicable. Some expenses are not imported as they are not relevant to the LOAD_INFO_TABLE.

    Again, it needs to be fully understood, this entire setup worked fine, error free, up to about a week ago.

    As a test, I did go back to older code (at least 2-3 versions back, where there is definitely some differences) on both the Load Input form as well as the Expense form (since no real changes ot the Expense form have been made there really was no need but did it just in case I overlooked something) and am experiencing the exact same problem where I guarantee all this worked perfectly fine at the time the older code was in production. So both current production and 2 versions back of code are experiencing the same issue. Therefore, I have to surmise something is wrong in the database.

    Once I load a record I can step forward, backward, 1st, last record and the form fills fine, no issue, no errors no complaints. the only issue is in the ADD NEW record event. Even if I make a change to the an existing record via the form I can update and save the changes fine. I am calling the ADD NEW from the standard navigation bar provided by VS and do nothing except check to ensure the record does not have changes (dirty() function) prior to adding the new record.

    Is there a proper repair for the SQL indexing that I can use? I tried a few items but it does not seem to resolve the issue. I used the MSSMS Reorganize as well as Rebuild tools but that did not resolve the issue.

    I did run "DBCC checkdb" and the following returned (not including everything just the last few lines:

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'PETS_Database'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I desperately need to understand and find this bug as soon as possible. Please anyone who can assist I would be eternally grateful.
    Last edited by K3JAE; Apr 6th, 2019 at 10:16 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width