THE INFORMATION IN THIS ARTICLE APPLIES TO:
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.
-
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.
-
Make a copy of the script, open the script in a text
editor, and then edit the following values:
-
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
-
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
-
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())
-
Save your changes to the file.
-
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.
-
Open a command prompt (click Start
> Run, type cmd,
then press ENTER).
-
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.
-
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:
-
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);
-
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;
-
Save your changes to the file.
-
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.)
-
Open a command prompt (click Start
> Run, type cmd,
then press ENTER).
-
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
-
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.