• EFT v 7.4.5 - 7.4.13.15

EFT v8.x instructions are provided in C:\ProgramData\Globalscape\EFT Server\SQL Server\Helper scripts\PurgeEFT.sql.

QUESTION

How do I purge the ARM database?

ANSWER

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.

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 procedure below describe how to use the provided SQL script to purge older data from the EFT ARM database. (There is also a procedure for an Oracle database below the SQL procedure.) The script allows for customization of the following parameters:

• The age of data to be purged. The script purges all transactions earlier than the desired data. By default, the script will purge all data older than 60 days.

• The number of transactions to delete at one time. By default the script will attempt to remove older transactions in "chunks" of 100,000 rows. If purging from an active database, it may be necessary to purge the records in smaller "chunks" such as 10,000 or 1,000 so as to not adversely affect the responsiveness of database for new transactions. The chunk size should be adjusted based on the load of the system at the time of purging and the average amount of data being purged.

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 C:\ProgramData\Globalscape\EFT Server\ folder.

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.

In EFT v8 and  later, instructions are provided in C:\ProgramData\Globalscape\EFT Server\SQL Server\Helper scripts\PurgeEFT.sql.  The scripts are in that same folder. (SQL files can be opened in Notepad, Notepad++, and other text file readers.)

In EFT 7.4.5 - 7.4.13.15, the scripts described below are provided for purging the database. If "PurgeSQLEFTData.sql" has never been run on the database, the fast purge will fail and display an error. See attached PDF for example of Fast purge results.

SQL Server Purge Script (for EFT 7.4.5-7.4.13.15)

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

1. In SQL Management Studio, run a backup of the database before doing any tasks. Right-click the name of the database, click Tasks, then click Back Up.

2. 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())

3. Save your changes to the file.

4. Use a command line tool such as "oSQL" to connect to the database and execute the script. Or click on the purge script in Management Studio. 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:\ProgramData\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.

After the purge is complete, you can shrink the database. In SQL Management Studio, right-click the name of the database, click Shrink, then click Database.

Oracle Purge Script (for EFT 7.4.5 - 7.4.13.15)

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.