Troubleshooting FME Server - HSQLDB Issues

From fmepedia

 (http://fmepedia.com/index.php/Category:FME_Server)

Before following these steps, you should consider using a production level database for your FME Server (Oracle, SQLServer, MySQL are all supported). The configuration steps for using a production database can be found in the FME Server Administrators guide in Appendix B. Be sure to back-up your repository database using the FME Server repository reader (Admin Guide Chapter 5 - Upgrading FME Server > Backing Up Your FME Server Repository Using FME Desktop) before doing this configuration change.

Removing Jobs from HSQLDB

At times it may be necessary to remove records and compact the HSQL database used by FME Server.

Before You Begin

It is recommended that you back-up your repository database before beginning. The database file itself can be backed up: <FMEServerDir>\Server\database\hsqldb\database\fmeserver.data. You can also back-up the FME Server repository with FME Server Repository Reader in FME Workbench (Chapter 5 - Upgrading FME Server > Backing Up Your FME Server Repository Using FME Desktop).

Important Note:

The steps below will remove job history from your FME Server database. If you or your organization needs to maintain job history for any reason this procedure should not be followed as is. Please contact fmeserversupport@safe.com to discuss various option.

Steps

1) Ensure that FME Server is running.

2) Go to a command prompt, navigate to this path and run this command:

<FMEServerDir>\Server\database\hsqldb\bin\runSQLTool.bat

3) From the SQL prompt these command can be used to clear out the various tables:

DELETE FROM fmeserver.fme_job_result;
DELETE FROM fmeserver.fme_job_request;
DELETE FROM fmeserver.fme_deliverable_jobs;
DELETE FROM fmeserver.fme_queued_jobs;
DELETE FROM fmeserver.fme_jobs;
COMMIT;
SHUTDOWN COMPACT;
\q

4) Restart FME Server twice.

Please Note:

If the database is particularly full you may run out of memory when attempting to clear out records. If this is the case the memory can be increased by editing the processmonitor config, use this line to increase startup memory: CMDStartFMEServer_1="C:\\Program Files\\FMEServer\\Server\\..\\Utilities\\jre\\bin\\FME_FMEServer.exe" -Xms32m -Xmx1024m -Xrs COM.safe.fmeserver.FMEServer "C:\\Program Files\\FMEServer\\Server\\fmeServerConfig.txt"|log|attempts=20

You will need to restart FME Server for this change to take affect.