SMS Error - Index Out of Date

“Database Index Out of Date” …

Moderate. The chance for data loss is Moderate, but the user is unable to use the database until it is corrected. Occassionally, if the errors are in the Estimate Details or Order Details tables, it is not uncommon to lose line items for the order/estimate that was corrupted. For many users, this error occurs frequently. For others, it almost never occurs.

The BDE maintains the “last modified date” on the database file (DB and MB) as well as the indexes (PX and ___). This error message occurs when the date of one of the indexes does not match the database file. This indicates that the data was updated more recently than the index, hence the index is “out of date”.

===== Possible Root Cause(s) =====

It goes without saying that this error should never occur. Cyrious' leading theory (and it is not confirmed) is that when two different clients are updating the same table nearly simultaneously, the following sequence happens:

- Client A updates the Data File

- Client B updates the Data File

- Client A updates the Index File

- Client B updates the Index File

except instead of being received in that order, the following sequence is recieved at the computer with the database …

- Client A updates the Data File

- Client B updates the Data File

- Client B updates the Index File

- Client A updates the Index File

From an IT point of view, the delivery order between two clients is not guarenteed. The result in this last scenerio is that the index date corresponds to Client A while the Data File date corresponds to Client B. Since the clocks on Client A and B are not syncronized to the millisecond, the BDE sees this as an “Out of Date” problem.

===== Resolution =====

* Reboot the Server First. This is very important to ensure that all of the file locks are removed, otherwise the rebuild may fail and data may be lost

* The primary fix for this issue is to use the dbrebuild utility to scan and rebuild the affected table.

* In rare cases, the indexes will be lost (removed) during the rebuild process. In this cases, it will be required to manually delete the indexes and insert the data into a new table with indexes. See Manually Rebuilding SMS Tables

* Another option when dbrebuild fails is the Borland Database Desktop utility. It uses the same DLL as the dbrebuild Utility but for reasons unknown is slightly better at salvaging databases with corrupted data.

===== Resolution 2 =====

- Open the NewData folder (If version 8.91 and above make sure the NewData tables are also updated to 8.91. A quick way to update is to rename NewData to 'Data' - meanwhile renaming Data to something else like 'Data' - and running SSLIP and SMS. The tables should update quickly during this process. Just don't forget to rename the folders again!)

- Sort by Type

- Scroll down to the last PX file

- Copy everything below the last PX file

- Paste in the Data folder

- Select NO to all (this replaces only the missing indexes

- dbrebuild Make note of the error logs in these cases and which index(es) are throwing the error. If the Index out of Date error returns after Resolutions 1 and 2 have been performed and the same index (example: CustomerOrderIndexDesc) is at fault then proceed to Resolution 3 which replaces the table entirely:

===== Resolution 3 =====

- Verify the affected table(s) using the Error Logs and, potentially, dbrebuild

- Open DBBrowser and create a Temporary Alias pointing to the NewData tables (see Step 1 of Resolution 2 if customer is 8.91 or above)

- Browse the relevant tables to make sure there is no data present in the NewData tables. Delete any records if necessary including Zero IDs - Run a query to insert data from the tables in the Data folder to the blank tables in NewData. The example at the end of this Resolution uses Order Database, but you will need to adjust this accordingly

- Verify in DBBrowser that the data transferred successfully to the NewData tables and then close DBBrowser - Remove the affected table(s) plus indexes from the current Data folder

- Copy over the NewData table(s) and indexes to the Data folder

- Run a dbrebuild scan on the table(s) to see if it needs to be rebuilt. If so, rebuild it - Launch SSLIP and SMS - confirm that the error no longer appears

SQL 1

INSERT INTO "\\server\Cyrious$\SMS\NewData\Adjustments Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Adjustments Database"

SQL 2

INSERT INTO "\\server\Cyrious$\SMS\NewData\Contact Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Contact Database"

SQL 3

INSERT INTO "\\server\Cyrious$\SMS\NewData\Customer Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Customer Database"

SQL 4

INSERT INTO "\\server\Cyrious$\SMS\NewData\Estimate Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Estimate Database"

SQL 5

INSERT INTO "\\server\Cyrious$\SMS\NewData\Estimate Details Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Estimate Details Database"

SQL 6

INSERT INTO "\\server\Cyrious$\SMS\NewData\Estimate Modifiers Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Estimate Modifiers Database"

SQL 7

INSERT INTO "\\server\Cyrious$\SMS\NewData\GL Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\GL Database"

SQL 8

INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Order Database"

SQL 9

INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Order Database"

SQL 10

INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Details Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Order Details Database"

SQL 11

INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Modifiers Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Order Modifiers Database"

SQL 12

INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Modifiers Database"
SELECT *
FROM "\\server\Cyrious$\SMS\Data\Order Modifiers Database"

</code>

You could leave a comment if you were logged in.