Troubleshooting - How to verify if Optimizer file corrupted or not

Problem Description:

Is there any way to figure out when the Optimizer cache is corrupted or if there’s a way we can avoid it altogether in the future?

Solutions:

Please refer below steps to identify if Optimizer file has the issue:

Method 1

1). Locate Optimizer file. The default path is %AppData%\QODBC Driver for QuickBooks\Optimizer)

You can find optimizer folder path from QODBC Setup Screen->”Optimizer” tab. Please copy this path & navigate to this path. 

2). Check file modified date time, if it's not recent - there is an issue.

To verify, start vb demo and query the table you use the most, e.g. Invoice or Bill  

3. Check file modified date time, if it's not recent - there is an issue.  

Method 2

1) Check QODBC logs. The default path is %AppData%\QODBC Driver for QuickBooks. 

Search for any of below errors. If one of below error is present in the log file, then Optimizer file is corrupted.

database disk image is malformed

SQLOptimize_SQLite_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

SQLOptimize_SQLOpen attach:
C:\Users\USER-NAME\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer\COMPANY-NAME.OPT
cannot start a transaction within a transaction

SQLOptimize_SQLite_ExecuteNonQueryFromMemory step:
ATTACH_PASSPHRASE ................. "C:\Users\USER-NAME\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer\COMPANY-NAME.OPT" AS "FQBase"
database is locked

SQLOptimize_SQLite_ExecuteNonQueryFromMemory step:
ATTACH_PASSPHRASE ................. "C:\Users\USER-NAME\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer\COMPANY-NAME.OPT" AS "FQBase"

Fixing Optimizer file/Rebuild Optimizer file

To overcome this issue, we need to reset optimizer file. Please refer below steps to reset your QODBC optimizer file:

1) Close all other applications that use QODBC.

2) Delete the Optimizer file: Please refer: How to Remove Optimizer file

Find the file with .opt extension at the path you had assigned to QODBC optimizer and delete the optimizer file.
(The default path is %UserProfile%\QODBC Driver for QuickBooks\Optimizer or %AppData%\QODBC Driver for QuickBooks\Optimizer )
You can find optimizer folder path from QODBC Setup Screen->”Optimizer” tab. Please copy this path & navigate to this path & delete .opt file.


3) After turning on the optimizer, please run below command in VB Demo:

sp_optimizefullsync All

Refer: How to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for Selected Tables
This command will bring some data to a local cache to increase retrieval performance for queries. I would like to suggest you to please let run above command completely. Above command may take some time depends on your records in QuickBooks. It may take 2-24 hours or more depends on the number of records in QuickBooks company file. Please let this command run completely do not kill this command. It is not advisable to kill/close the application while it's building the Optimizer file.

4) Please test again.

5) If you are still facing the issue, Please raise a support ticket.

Also Refer

Troubleshooting: Slow Performance / Cannot Get Full Records From QuickBooks / How to find missing data

Keywords: qodbc cache slowing down data access