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 is there’s a way we can avoid it altogether in the future?

Solutions:

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

Method 1

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

You can open the optimizer folder via Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >> Go To "System DSN" Tab, select "QuickBooks Data" or "Your created QODBC" DSN & click "Configure" >> Navigate to Optimizer tab->Click On "Open Optimizer Folder" 

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

To verify, start QODBC Test Tool 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 the below errors. If one of the below errors is present in the log file, then the Optimizer file is corrupted.

the 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 the optimizer file. Please refer to the 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 the .opt extension at the path you had assigned to the 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 the optimizer folder path from QODBC Setup Screen->”Optimizer” tab. Please copy this path & navigate to this path & delete the .opt file.


3) After turning on the optimizer, please run the below command in QODBC Test Tool:

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, please let run the above command completely. The above command may take some time, depending on your records in QuickBooks. It may take 2-24 hours or more, depending on the number of records in the 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