Database Maintenance

The database maintenance tool checks the database for improper settings, inconsistencies, or corruption.

In the Main Menu, click Tools, Database Maintenance.

You can then choose which fixes or tools to run, or follow the provided guidance to correct problems. It should only be run on one computer at a time.

Note:
  • Warning. We do not recommend running Database Maintenance during office hours because it can cause severe network slowness.
  • Database maintenance repair log files are saved to the A to Z folder in the DBMLogs subfolder.
  • If using replication, see Database Maintenance on Replication for instructions.


Checks Tab

All possible database methods are listed.

Every time any check or fix is run, MySQL will check every single table for corruption, so you may notice a delay. As checks are complete, the Results log will update.

Some database methods only need to be run once. Once run, they will automatically be moved to the Old tab.

Note: Galera enterprise users can manually disable the MySQL integrity check by setting the DatabaseMaintenanceSkipCheckTable preference to true. The Optimize button under the Tools tab can be disabled by setting the DatabaseMaintenanceDisableOptimize preference to true.


Print Log Results: Click Print to send all results currently displaying to the printer.

Manually Fix Errors: Some errors detected during a check must be fixed manually. If the results log indicates a manual fix is needed, double-click on the row to open more details.

This window is non-modal. It can remain open while you access other areas to fix the issue. Close the main Database Maintenance window, then proceed. Click Print to print the results. See Manually Fix DBM Errors for a list of errors and how to fix them.

Hidden Tab

Each database maintenance method can be hidden so it no longer shows in the list of Database Methods. This can be useful for methods your office doesn't use. Hidden methods show in the Hidden tab.

To hide a method, on the main Database Maintenance window, right click on a method and select Hide.

To unhide a method, right click on the method and select Unhide.

Old Tab

Database maintenance methods that are no longer needed or outdated are moved to the Old tab. Methods can still be run from this window, but only do so if instructed by Open Dental HQ as they can be unnecessary and time consuming.

Tools Tab

Each database maintenance tool addresses a specific issue. Before running a tool, read instructions carefully. Typically they take a long time to run but only need to be run once.

Update in progress on computer: Manually clear the Update in Progress preference. This should be used when an update is complete but workstations are not able to log in due to "update in progress" error.

Ins Pay Fix: This tool will fix insurance payments that have been received but not finalized (attached to insurance checks/payments). One check will be created for each claim. The claim payment date will be the date the claim was received.

Optimize: This tool will backup, optimize, and repair SQL commands on each individual table. Only use if corruption is suspected or for extremely large databases.

Appt Procs: This tool will fix procedure descriptions on appointments that are not correctly showing tooth numbers for some reason. It will also update procedure code text colors, and previous date information for existing appointments, if you have Proc Appt Colors for the appointment box.

Spec Char: Only use this tool if you are having trouble syncing your appointments to the Mobile Synch App or if you receive an invalid character error while using the Middle Tier. It will permanently remove unsupported Unicode characters from appointment notes, procedure descriptions, patient address notes, patient address fields, adjustment notes, payment notes, and definition names. This will also remove characters from non-English languages. If you wish to continue, the password is fix.

InnoDb: This tool will convert all tables in the database to the selected storage engine (MyISAM or InnoDb). All users are initially set up as MyISAM. Users only switch to InnoDb if they have special needs, such as increased performance. User must have done research on MySQL website.

Note: InnoDb is not an option for Open Dental Cloud users.

Tokens: This tool will check the database for any X-Charge tokens that are attached to the wrong credit card number. A second window will come up listing all credit cards and letting you decide which ones to check.

Remove Nulls: This tool will remove extra, hidden characters that may be causing errors or problems and replace with empty strings.

Etrans: This tool will clear etrans messages (electronic transactions) older than one year. An automatic backup will be created first. It can only be run by users with the Security Admin permission.

Active TPs: This tool will create active treatments plans for patients that have treatment planned procedures but no active treatment plan. This was a known issue in early beta versions of 15.4.

Raw Emails: This tool can be useful if you are using the Email Inbox and backup size has grown too large. Large size is often due to the emailmessage table. Run the tool to look through all inbound emails, safely remove raw message content that is no longer needed, then optimize the emailmessage table.

Email Attaches: This tool will rename and move email attachments that are in the base EmailAttachments folder into the intended location in the In and Out subfolders. Resolves file not found errors when trying to view attachments.

Recalc Estimates: This tool will identify patients with at least one estimate that belongs to a dropped insurance plan. For each such patient, estimates will be recalculated using their current plan information and will delete estimates associated with dropped plans.

Pay Plan Payments: This tool will detach patient payments that have been applied to Insurance Payment Plans, and insurance payments attached to Patient Payment Plans.

Balance Families: Running this tool requires a password known only by the Open Dental Conversions Team. This tool will create a series of income transfers between patients in the same family to correct situations where some family members have a credit and others have a balance due. It may take a long time to complete and impact performance for Open Dental users on other workstations. Contact Support for assistance if you are unsure if this is needed for your database.

Redundant Indexes: This tool removes redundant and unnecessary indexes from all tables in the database.

Patient Missing: This tool is used when database corruption has caused patients to go missing. When possible, missing patients can be re-added.

Ortho Duplicates: This tool deletes duplicate cell entries within the Ortho Chart. Run this when there is a noticeable time delay when loading or saving a patient's Ortho Chart.

Repeat Charge: Option only visible if the logged-on user has the Repeating Charge Tool permission. This tool updates the Repeating Charge amount for all patients or, just patients within a Super Family at once. Only updates repeating charges with the same procedure code, current charge amount, and repeat charge status (i.e. enabled or disabled). Highlight the row and click Fix to open the Multi Repeat Charge tool.

Enter the information for the current repeating charges that need the charge amount updated.

Click Run to update the repeating charges. When the tool finishes, a confirmation message will show, indicating the number of repeating charges that were updated. A log file is also saved to the desktop with more information.

Note: If the Repeating Charges tool already ran for the day, running the tool again on the same day after the update will not post duplicate charges for the affected patients.

Troubleshooting

Why do I get the message: Log not saved to Repairlog.txt because the user does not have permission to access that file.

IT needs to grant the user permission to the DBMLogs folder (typically C:\OpenDentImages\DBMLogs).

Why do I get the message: WARNING! More than 50 workstations are connected to this database. Running DBM may cause severe network slowness. We recommend running this tool when fewer users are connected (possibly after working hours). Continue?

More than 50 workstations are connected to the database. We recommend waiting until after office hours to run Database Maintenance as it can cause slowness.