Database maintenance

Tue, 09/18/2018 - 12:29 By Dave Brooks

RPM Remote Print Manager® ("RPM") has been using a database for jobs and configuration since at least February 2008, although longer than that in beta releases. We have provided utilities for database maintenance since the early days.

Over time we continue to add to the suite of tools as we talk with customers and strategize behind the scenes.

Where is the RPM database?

The RPM database is normally called “RPM.FDB” and is kept in
C:\ProgramData\Brooks Internet Software\RPM

How do I control the location?

In the user interface, choose General Settings from the Configure menu.

You’ll see the General Settings dialog:

Note that the Find and Move buttons are marked in this image.

When you click the Find button, the UI opens a Windows Explorer directory window on the folder containing the file RPM.FDB.

When you click the Move button, the UI opens a Windows Explorer window titled “New settings database file”. You can select a new target folder and filename for the database to be kept. We suggest you use caution and:

  1. Make sure the folder is on your local disk, not on a network share
  2. Make sure the folder has an exception from your virus scanner

RPM at times keeps this database very busy and you don’t need a virus checker to “make sure” everything is OK with that. If your antivirus software interferes with the database, there is not much we can do to help except suggest you add an exception for the database.

How do I tell RPM where the database is located?

You don’t need to tell RPM where the database is located. The database folder is assigned when the RPM installer runs.

How often do I need to maintain the database?

It depends entirely on how heavily you use RPM. We recommend that you do this at least monthly, but weekly is even better in busy environments. There's no need to wait for something to go wrong.  We do not recommend you determine the need for database maintenance because of the size of the database.

How do I run the utilities?

First, open a command prompt with admin privileges. Make sure it’s elevated or you’ll fail to create a backup and wonder why.

Go to the RPM install folder. Normally this is:

C:\Program Files\Brooks Internet Software\RPM

Shut down the service “RPM Remote Print Manager” with the services control panel, or run this command at the command prompt:

net stop rpm

Also, make sure the user interface is closed. If you can’t find it or are not sure, look for an icon with a yellow R in the task tray, or use the task manager to look for “rpmgui.exe”

Make sure neither of those programs is currently running, otherwise the scripts will fail.  In some situations, the RPM service may not stop gracefully.  It is acceptable to force the RPM service closed using task manager in these situations.

Run the script in the install folder: fixdb.bat, and review the output.

fixdb script w/ error

Note that this time it reported an error running gfix.exe stating “secondary server attachments cannot validate databases”. For demonstration, I deliberately left the service running while executing fixdb. If you see this, you know there is a program open using the database.

After stopping the service and running fixdb.bat again:

fixdb results

Here the program reported some errors and marked them for removal.  This program does not actually remove the errors.

Out of curiosity, you might get the size of the RPM database using the command prompt, then run shrinkdb, and finally, get the database size again.

shrinkdb

You should observe a smaller database size when shrinkdb finishes.  If not, there is no cause for alarm.  It could mean that there was very little empty space in the file.  Five megabytes is about the size of an empty RPM database. If yours shrinks to 12 or 20 MB or even larger, you’ll be fine. On this particular day I haven’t printed much since last weekend, so the message log would have been automatically cleared out. Note that reducing the number of log messages on its own doesn’t reduce the size of the database file at all.

Again, we recommend you run database maintenance on a regular schedule and not use the size of the database as a guide to know when it is appropriate to do so.  If you have never run maintenance or do not know when it was ran the last time, run shrinkdb to be on the safe side.

What database maintenance does RPM do on its own?

Every night at midnight, if RPM is running, it will initiate a “sweep” on the database. Here is an example of RPM performing the sweep twice over a recent weekend:

Database sweep

Note that it takes roughly 22 milliseconds, although it could certainly take longer on a larger database.

Tell me more about “Repair Database” and “Purge Database” in the UI File menu

The “Repair Database” form provides a number of options for regular database maintenance. Aside from the fix and shrink scripts mentioned above, this might be the top choice.

Database Maintenance

Let’s select “Validate” then click OK. The UI confirms whether you want to stop the service.

Confirm Maintenance

Assuming that you do want to proceed, the maintenance utility runs and finally, you see the results:

Database Maintenance results

The text in this dialog box is already copied to the clipboard. If you wanted to make a record of this or send an email to our support technicians, you can paste the text without trying to select and copy it from the dialog.

Running the fixdb script is roughly equivalent to doing a database repair with the three options “Validate”, “Mend” and "Sweep" selected. Note: you can run fixdb or use this Repair form but you don't need to do both.

Also, if you run "Validate" and "Mend" and any errors are reported, you need to run "Sweep" to clean those errors out of the database file.

The “Backup and Restore” option will make a backup of your current database, create a new database, and migrate your old settings to it.

The “Create a new database and migrate settings” option does what the name suggests. It leaves you with a new database with your settings. All the job numbers, message log IDs and related “counters” are reset. I normally do this after a period of hard testing where I might run a quarter to half a million jobs through RPM, or more. I usually do this when I’ve been trying to recreate errors so I can fix them, and this has been a good way to start with a clean slate. It seems not many people use this option but it works for me.

The “Purge Database” function is fairly severe. It makes a backup database with your current settings, jobs, log messages and more, then makes a new RPM database which is completely empty. By “empty” we’re talking about, no configuration at all.

You might do this if you are thinking you have database problems. I would first export all my settings before I purged the database. In fact, I am not sure I’ve needed to do this more than once or twice in ten years.

How large should a database get before you need to purge?

Purging the database will completely remove all of your RPM settings including your queues, so you should only do so as a last resort such as if you encounter database errors that cannot be repaired.

The excellent Firebird website is a great resource for learning more about the purpose and capabilities of the fgix and gbak applications mentioned in this article.