Knowledge Base

What’s the difference between Emergency Purge and Purge?

• Emergency Purge is an extremely high priority task.
  It requests the SQL Server engine to delete a certain amount of data as soon as possible. This task runs with a higher priority than other activities on the Database (read, write etc.). If it takes a long time you can imagine the delay that could easily result.
• Purge is a low priority task. It requests the SQL Server engine to delete a certain amount of data by small increments so as not to saturate the SQL Server engine and nor block other activities on the Database (read, write etc.). This type of Purge must be run regularly on a maintenance schedule so as not to have too much data to erase at a time and to make it as unobtrusive as possible.

How to control the Purge?

This will essentially depend on the system that one puts in place. It is not easy to determine since there is no ready-made ‘recipe’ to apply. However, bear in mind that the role of the Purge task is to minimize the need for Emergency Purging in normal operation.

NB: Even in a well controlled system the Emergency Purge is useful to safeguard against the Database space ever filling up too quickly during particular operations.

Example of configuration:

• The database is up to 3GB.
• An Emergency Purge occurs at 80% full and purges 10% of the records.
• In a normal cycle, there is a median flow of 120 log entries per second.

First step – estimating the maximum number of records

The first step consists in estimating the number of records that could be inserted in the database before reaching its maximum size. This size will depend on the stored attributes and of the fragmentation in the database. To work out this number, you can let the PcVue project run with the Emergency Purge deactivated. When the limit is reached, the HDS will get be paused and you need only check the record count in your Log and Trend tables.

In the present example, there are 13,970,595 records in my 3GB Database with a flow of 120 records per second.

This corresponds to a recorded time of 32 hours and 20 minutes approx.

• (13,970,595 / 120) / 3,600 = 32.339 hours

One can deduce that the Emergency Purge would be launched 6 hours and 30 minutes before the database is full:

• (32.339 * (100-80) / 100) = 6.4678 hours

One can also deduce some that 80% of the full database corresponds to 11,176,476 log entries:

• (13,970,595 * 80 / 100) = 11,176,476

The Emergency Purge task will delete 10% of the database at 80% full. That means that the Emergency Purge is going to erase 1,117,647 log entries in 2 hours and 40 minutes:

• 11,176,476 * 10 /100 = 1,117,647.6
• (1,117,647.6 /120)/3600 = 2.587 hours

Second step

We now have all the information needed to tune the Purge to suit the Emergency Purge.

The Emergency Purge will release 32h 20m – 6h 30m, that’s around 25h 50m of log entries.

To be on the safe side in configuring the Purge, we arbitrarily choose to retain up to 24 hours of data and to run this task every hour

• Oldest data => 24 hours
• Running the maintenance schedule => every hour

In the best case the HDS will therefore only purge one hour of data (432,000 log entries) while retaining a minimum extent of data of 24h and a maximum of 25h 50m.

This way, we have a safety margin of 50 minutes of log entries between the Purge and the Emergency Purge, which amounts to 360,000 log entries.