A few months back we sent you a top tip with simple pointers to clean up the settings on your EFT Server. This prompted several of you to book EFT health checks, where we uncovered many users with the Auditing and Reporting Module (ARM) were not purging the database.

ARM is an optional module with over 40 pre-configured reports and a visual custom report designer. It has everything you need to perform a file transfer audit. EFT stores every transaction, file transfer and administrative change as an entry in the ARM database. But as a result, databases get very large. With so much data to filter, reporting can take longer than usual. Keeping your system clear improves response times.

Globalscape provide two scripts to help purge your database of “old” entries. These are stored under the “SQL Server” folder of your configuration, and are called PurgeSQLEFTData.sql and Fast_SQLPurge.sql .

PurgeSQLEFTData.sql will remove data older than 60 days from your database in 100,000 row “blocks”. This has a relatively low impact on your database.

Fast_SQLPurge.sql just cleans up everything in one process which – depending on the amount of data in your database – can put a significant load on your server. The ARM database stays active throughout the process and if it stops accepting connections for any reason, all transactions will be written to a file which will be streamed back into the database when the connection is re-established. This will not impact your users uploading and downloading data to your server.

But before purging your database you should make sure that any data needed for compliance and auditing purposes is backed up accordingly. Your Database Manager should be able to advise on the database backup retention period.

 

Preparing the PurgeSQLEFTData script for use

The scripts are ready to run with just a couple of changes. At the top of the script is a line that defines the database “USE EFTDB”. If you are using a different database then you need to change this line to reflect the correct name. (The database name can be found on the “logs” tab of your server configuration).

If you wish to change the number of days the purge will date from, then change the default “60” in the line “SET @stopTime = DATEADD(DAY, -60, GETDATE())” to the required number of days. So, for example, if you wish to delete data more than 30 days old, change this to 30.

Once this is done, save the script and you can run it against the database.

Automating the script using EFT

SQL Server comes with a utility called “SQLCMD” which enables you to run SQL scripts from a Command Line.

Building this as a “command” inside EFT would use the command line similar to
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.exe
and the parameters of
-E -i “C:\ProgramData\Globalscape\EFT Server Enterprise\SQL Server\PurgeSQLEFTData.sql”
If you use SQL Authentication for your database then replace the –E with -U <DBUsername> -P <Password>

The output of the file can be written to a text log file so you can see how many records have been purged from the database.
Build this command into a scheduled Event rule and EFT will keep its own database purged back up as needed. If you have EFT Enterprise or the Scheduled Timer Module for EFT Express, you can build this command into a scheduled Event rule.

When we ran this against our demo system, we cleaned up over 300,000 rows clearing nearly 70% of space. The savings on a production system should be even better.

Did you find this useful?

Receive tips and news in your inbox