Knowledgebase
[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 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.
 

http://support.flexquarters.com/esupport/newimages/Optimizer/step1.png

 

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 
 

http://support.flexquarters.com/esupport/newimages/Optimizer/step2.png

 

http://support.flexquarters.com/esupport/newimages/Optimizer/step3.png

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. 

 

http://support.flexquarters.com/esupport/newimages/Optimizer/step4.png

 

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

(0 vote(s))
Helpful
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.
Jack
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:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).