Knowledgebase: Setup
[QODBC-Desktop] Troubleshooting - How to verify if Optimizer file corrupted or not
Posted by Jack - QODBC Support on 05 March 2015 06:33 AM

Troubleshooting - How to verify if the Optimizer file is corrupted or not

Problem Description:

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


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

Method 1

1). Locate the 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 the "System DSN" Tab, select "QuickBooks Data" or "Your created QODBC" DSN & click "Configure">> Navigate to Optimizer tab->Click On "Open Optimizer Folder."


2). Check the 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 the 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."
Is the file encrypted, or is it 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 query retrieval performance. I want to suggest you, please let run the above command entirely. 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 documents in the QuickBooks company file. Please let this command run entirely; 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 still face 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

(0 vote(s))
Not helpful

Comments (2)
Nathan Arendt
09 July 2016 12:50 AM
I have been able to 'fix' a corrupt file by restoring a back up of the file before it became corrupt.

If you have a small QuickBooks file it doesn't really matter but the file I work with is over 2.4 GB and it takes hours and hours to reload the optimizer file.
11 July 2016 07:33 PM
Hi Nathan,

You can take backup of Optimizer file in your backup folder after Optimizer file build up. This might help you if optimizer file gets corrupted, You can save the number of hours for building new optimizer file if you have this file. You can restore backup in case of Optimizer corruption. You need to remove corrupted .opt file & place Optimizer backup file in your Optimizer path (the default path is %AppData%\QODBC Driver for QuickBooks\Optimizer). After restoring .opt file you need to run sp_optimizeupdatesync ALL for getting delta changes. This command will synchronize the all table data with the QuickBooks data file using changed and deleted data. It is useful for making sure that the optimizer is up to date with the QuickBooks company file
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).