How to deploy the default Contact Expert database scheduled maintenance jobs
CE Preventive Maintenance Routine
This article is one step in the Preventive maintenance routine customers are to follow on a tight schedule to make sure their Contact Expert system is fully operational.
Contact Expert databases can grow fairly large over short periods of time depending on the business use case – number of operators, number of customer transactions, etc. Since a lot of operational configuration, the entirety of customer contact data as well as all the historical information are stored in there, it is especially important to maintain a healthy, responsive database subsystem. It is in our customers' best interest to apply their corporate database maintenance policies to Contact Expert's databases, however Geomant provides a default maintenance routine in the form of optionally deployable scheduled job scripts found on the installation media.
Default Database Maintenance Job
The default maintenance job Geomant provides on the Clean Installation package (ISO file) sets up SQL Server scheduled jobs that are performing the following tasks on a periodic basis:
- Physically erase certain information from the CE databases (ACESystem and ACEReport) with a certain age.
- Archive – physically move – certain information in the CE databases from their original table to special 'archive' tables.
- Perform database optimizations – index rebuilds – on database tables with the most frequent traffic and the most data.
Controlling the Age of Data to Archive and Purge
These default maintenance jobs are using a predefined set of age values from the ACESystem.DBMAINTENANCE and ACEReport.DBMAINTENACE tables. These tables have a single record with the following values:
| Threshold | CE DB | Description |
|---|---|---|
| DAYS TO KEEP RECORDS | ACEsystem, ACEReport | This is the age (in days) of the oldest data that is kept in the key tables. The default value is 1095 days. |
| DAYS TO CACHE RECORDS | ACEsystem, ACEReport | Data older than this age (in days) – but younger than DAYS_TO_KEEP_RECORDS – are moved from the standard tables to 'archive' tables. The default value currently is 438 days. |
| DAYS TO KEEP AUXILIARY RECORDS | ACEsystem, ACEReport | A special set of historical data older than this age (in days) – email and SMS task info that were handled by agents, therefore can be considered 'closed' – are physically removed from the relevant historical tables. The default value is 90 days. |
| DAYS TO KEEP PARKED EMAILS | ACEsystem | This is the age (in days) how long we keep parked emails data. The default value is 90 days. |
| DAYS TO KEEP DRAFT EMAILS | ACEsystem | Draft emails older than this age (in days) are removed. The default value is 30 days. |
| DAYS TO KEEP ALARMS | ACEsystem | Alarms older than this age (in days) are removed. The default value is 90 days. |
Archiving database records effectively pulls them out of usage – in that sense this step is similar to an actual deletion. Data in the _ARCHIVE tables are not used by CE.
Would the business rules – including national laws – affecting your particular contact center mandate higher age thresholds for data retention, you need to manually modify these figures in the respective DBMAINTENANCE table.
Affected CE Tables
The above age thresholds are affecting the following tables:
- ACESystem.ALARMS_CLEARED
- ACESystem.ALARMS_RAISED
- ACESystem.HISTORY
- ACESystem.HISTORY_ARCHIVE
- ACESystem.EMAILOUT
- ACESystem.PARKED_EMAILS
- ACESystem.EMAIL_ACTION_HISTORY
- ACEReport.CALLSTAT
- ACEReport.CALLSTAT_ARCHIVE
- ACEReport.CAMPAIGNSNAPSHOT
- ACEReport.CONTACT_BATCH
- ACEReport.AGENTSTAT
- ACEReport.AGENTSTAT_ARCHIVE
- ACEReport.PREDICTIVESNAPSHOT
Data in Predictive Reports
Predictive reports (Overdialing Rate, and Nuisance Rate) rely on statistical data accumulated on a periodic basis in the ACEREport.PREDICTIVESNAPSHOT table. The maintenance job is deleting data from here that are older than the ACEReport.DBMAINTENANCE.DAYS_TO_KEEP_AUXILIARY_RECORDS threshold. You can use this to control the amount of data these reports pull up from the past.
Note
The amount of data accumulating in the PREDICTIVESNAPSHOT table is fairly large! Be conscious of available storage when setting the relevant maintenance job threshold to a value higher than the default!
Step-by-step guide
Please follow these steps to deploy the default maintenance jobs onto the SQL Server serving the Contact Expert databases:
- Log on to a computer where the SQL Server Management Studio software is already installed and log in to the SQL Server running on the CE Database Host with a DBA level login account ("sa").
- Locate the CE\CE_DB\Jobs folder on the Contact Expert Clean Installation media (ISO).
- Open the CESystem_Maintenance_Job.sql file and the CEReport_Maintenance_Job.sql file in two separate Query Window of the SQL Server Management Studio application.
- Click into the Query Window housing the CESystem_Maintenance_Job.sql script, click Control_U – or click into the Available Databases dropdown menu – and make sure the ACESystem database is selected. This will tell the script to perform its operation on this database which is where we will deploy the maintenance job first.
- Click the Execute button. There should be no error messages presented out of the execution of the script. There is now a new scheduled job created on the SQL Server complete with a predefined schedule (e.g. weekly execution at 01:00am on Sundays, etc).
- Repeat steps 4 and 5 with the CEReport_Maintenance_Job.sql script.
- Make sure the data retention thresholds in ACESystem.DBMAINTENANCE and ACEReport.DBMAINTENANCE tables are in line with the business rules / national laws in effect for you!