Database maintenance
Screenshots in this article are from RPM 7.0.
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?
In RPM 7 the database is normally called RPM.fb5 (RPM 6 used RPM.FDB) and is kept in
C:\ProgramData\Brooks Internet Software\RPM
How do I control the location?
In the user interface, choose Settings > General.
You’ll see the General Settings dialog:
The database buttons are Open Folder… and Move Database…, below the folder paths. (In RPM 6 they were Find and Move.)
When you click Open Folder…, the UI opens a Windows Explorer window on the folder that holds the database.
When you click Move Database…, you can select a new target folder and filename for the database to be kept. We suggest you use caution and:
- Make sure the folder is on your local disk, not on a network share
- 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 database maintenance?
In RPM 7, everything runs from the user interface. Choose File > Database > Repair Database, pick what you want done, and click OK. See Repair Database below for what each choice does.
You don’t need to stop the service yourself. RPM asks to stop it, runs the maintenance, and starts it again afterwards. Print jobs are not received or processed until it finishes, so pick a quiet time. Maintenance has to run on the computer where RPM is installed, with administrator rights; if the user interface isn’t running as an administrator, Windows asks for permission first.
Coming from RPM 6? RPM 6.2 shipped two command-line scripts, fixdb.bat and shrinkdb.bat. RPM 7 doesn’t have them. Use Full Validate and Sweep in place of fixdb, and Backup and Restore in place of shrinkdb.
If you have never run maintenance, or don’t know when it last ran, run Backup and Restore to be on the safe side. It also reclaims empty space in the database file. Don’t be alarmed if the file barely shrinks; that only means there wasn’t much empty space. We recommend maintenance on a regular schedule rather than using the size of the database as a guide.
How do I back up the database?
fbbackup-50.exe, in the RPM program folder, saves the whole database to a zip file while RPM keeps running. From a Command Prompt run as administrator:
cd "C:\Program Files\Brooks Internet Software\RPM"
fbbackup-50.exe -o "D:\Backups\rpm-backup.zip"
Add -x MSGLOG to leave out the message log, which can make the backup much smaller. To back up on a schedule, create a Windows Task Scheduler task that runs the same command as an administrator account.
Keep rpm.key, from the same folder as the database, with your backups. It decrypts the passwords saved in RPM’s configuration; a backup restored without it loses them.
To restore, stop the service and run fbimport-50.exe. This replaces everything in the database with the backup:
cd "C:\Program Files\Brooks Internet Software\RPM"
net stop rpm
fbimport-50.exe -i "D:\Backups\rpm-backup.zip"
net start rpm
What database maintenance does RPM do on its own?
Every night at midnight, if the RPM service is running, it does a little housekeeping on its own:
- It checks the database’s health and writes the results to a report.
- It moves the message log entries from earlier days out of the database. Each day’s entries are saved as a zipped CSV file, along with a summary report of that day.
- It runs a “sweep” on the database, which clears out old record versions so the database stays fast.
- It records the database’s statistics in one more report.
You don’t need to set anything up for this, and you don’t need to stop RPM. The nightly sweep is why RPM 7 turns off Firebird’s own automatic sweep, which could otherwise start in the middle of a busy day.
The files go in a REPORTS folder beside the database, normally C:\ProgramData\Brooks Internet Software\RPM\REPORTS. A backup of the database folder picks them up too. Each file name ends with a date:
- HEALTH-date.txt: the database health check
- REPORT-date.txt: a summary of that day’s message log
- MSGLOG-date.zip: that day’s message log entries, as a CSV file
- GSTAT-date.txt: database statistics
RPM keeps 30 days of these files and deletes older ones. If our support staff ask about your database, these reports are a good thing to send them.
Tell me more about “Repair Database” and “Purge Database”
In RPM 7 both are under File > Database. The “Repair Database” form is where you do regular database maintenance. It has two sets of options, Repair Options and Shrink Options, and both start at None.
Let’s select “Full Validate” then click OK. The UI confirms whether you want to stop the service.
Assuming that you do want to proceed, the maintenance utility runs and finally, you see the 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.
Under Repair Options you pick one of Full Validate, Mend or Sweep at a time. Validate and Sweep are safe to run as often as you like. Mend can lose data, so save it for a database that is damaged. If Full Validate or Mend reports errors, run Sweep afterwards to clean them out of the database file.
Under Shrink Options, 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 Firebird tools RPM uses behind the Repair Database form, gfix and gbak.