Purging Data from the Database

Space requirements for transactions in the ARM Database depend on the estimated EFT activity, number of users, and installed modules. A general estimate is 3MB to 5MB of per 1000 files uploaded. A minimum of 3GB hard drive space is recommended for the initial database size, with additional space required for growth over time. For more detailed information on sizing estimates, refer to Knowledgebase article #10684: How much disk space should I allocate for the Auditing and Reporting module (ARM)?

A good database maintenance plan is important for keeping space requirements to a minimum. Such a plan should include periodic archiving or purging of older database. Users should follow standard SQL Server/Oracle tuning guidelines provided in the database vendor documentation to maintain a healthy database.

The following procedure describes how to use the provided SQL script to purge older data from the EFT ARM database. The script allows for customization of the following parameters:

Purge Script

Database-specific purge scripts are installed with the EFT. By default, the scripts will be under the "SQL Server" and "Oracle" sub-directories of the EFT Application Data Path. By default the Application Data Path will be "C:\Documents and Settings\All Users\Application Data\GlobalSCAPE\EFT Enterprise" or "C:\Documents and Settings\All Users\Application Data\GlobalSCAPE\EFT Enterprise" on Windows 2008.

It is recommended that the purge script be configured and run on a periodic basis to ensure the database size does not grow uncontrollably. The script may be run manually or automatically using the operating system scheduler, scheduling functionality within the database, or using a Scheduler (Timer) Event within the EFT. It may be useful to add the script execution as an additional step to the default "Backup and Cleanup" Scheduler (Timer) Event in EFT.

SQL Server Purge Script

The default SQL Server purge script is <EFT>\SQL Server\PurgeSQLEFTData.sql.  

  1. If you need to change the database name, "chunk" size, or age at which to purge transactions, make a copy of the script, open the script in a text editor, and then edit the following values:

    1. If the database name is not EFTDB, modify the following line with the name of the database:

      USE EFTDB

      For example, if your database name is "EFTDB_001" you would change the line to:

      USE EFTDB_001

    2. To change the "chunk" size from the default value of 100,000, modify the following line with the desired chunk size:

      EXEC sp_PurgeEFTTransactions NULL, NULL, 100000, 1

      For example, if you wish to purge in chunks of 10,000, you would change the line to:

      EXEC sp_PurgeEFTTransactions NULL, NULL, 10000, 1

    3. By default, the script will purge all transactions older than 60 days. To specify an alternate age, modify the following line with the desired age by dates:

      SET @stopTime = DATEADD(DAY, -60, GETDATE())

      For example, if you wish to purge transactions older than 30 days, you would change the line to:

      SET @stopTime = DATEADD(DAY, -30, GETDATE())

  2. Save your changes to the file.

  3. Use a command line tool such as "oSQL" to connect to the database and execute the script. The example below assumes you have kept the same name for the file.

    1. Open a command prompt (click Start > Run, type cmd, then press ENTER).

    2. Type the following to execute the SQL script:

      <PATH>\oSQL.exe -S [server address] -U [username] -P [password] -i "<PATH>\PurgeSQLEFTData.sql"

      For example, type:

      "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\oSQL.exe" -S 192.168.19.17 -U iuser -P asd123!f$s1 -i "C:\Documents and Settings\All Users\Application Data\GlobalSCAPE\EFT Enterprise\SQL Server\PurgeSQLEftData.sql"

For additional information on the oSQL utility, including common script samples, refer to osql Utility on microsoft.com.

Oracle Purge Script

The default Oracle purge script is <EFT>\Oracle\PurgeOracleEFTData.sql.

  1. If you need to change the "chunk" size or age at which to purge transactions, make a copy of the script, open the script in a text editor, and then edit the following values:

    1. To change the "chunk" size from the default value of 100,000, modify the following line with the desired chunk size:

      CALL sp_PurgeEFTTransactions(NULL, NULL, 100000, 1);

      For example, if you wish to purge in chunks of 10,000, you would change the line to:

      CALL sp_PurgeEFTTransactions(NULL, NULL, 10000, 1);

    2. By default, the script will purge all transactions older than 60 days. To specify an alternate age, modify the following line with the desired age by dates:

      pEndTime := sysdate - 60;

      For example, if you wish to purge transactions older than 30 days, you would change the line to:

      pEndTime := sysdate - 30;

  2. Save your changes to the file.

  3. Use a command-line tool such as "sqlplus" to connect to the database and execute the script. (sqlplus.exe may be obtained by installing the Oracle Data Access Components (ODAC) on the system at which the script will be executed. sqlplus.exe may require your tnsnames.ora file to be properly configured to connect to the EFT database. The example below assumes you have kept the same name for the file.)

    1. Open a command prompt (click Start > Run, type cmd, then press ENTER).

    2. Type the following to execute the file:

      <PATH>\sqlplus.exe <EFT>/<EFT>@<EFT>

      For example, type:

      "C:\app\Administrator\product\11.2.0\client_1\sqlplus.exe" iuser/ asd123!f$s1@EFTDB

    3. The sqlplus console starts. At the prompt type the following, then press Enter:

      @<PATH>\PurgeOracleEFTData.sql

      For example, type:

      @C:\MyScripts\PurgeOracleEFTData.sql

For additional information on the sqlplus utility refer to SQL*Plus User's Guide and Reference on oracle.com.