Introduction

After a certain amount of time, all application servers collect cluttered files, left-over project resources and other useless data, often from years of previous production. This can really start to slow down the host environment, and even impact production.

Here is a complete guide to getting rid of this unused data.

Step 1 : Improve server speed by freeing up RAM
Step 2 : Free up SQL space with CcaCleanup.exe
Step 3 : Free up hard drive space on the servers (work in progress)

Step 1

Improve server speed

You can improve your server speed by freeing up RAM.

1. Identify all surveys that are currently not in production, as well as their associated sample lists and agent groups.
2. Connect to CCA via the Supervisor client
3. (Optional) Create backups of the survey tasks using the task package feature
4. Select all surveys that have been identified and hit your Delete key or right-click -> delete
   During this action, you might receive the following prompt : 
   
   Clicking "Yes" will also remove the survey from its web-position, and thus free up one spot to upload a new survey for CAWI.

Repeat for the identified sample lists and agent groups.

Step 2

Free up SQL space

In this section, you will see how to free up SQL space with CcaCleanup.exe.

After Step 1, your data has been removed from the CCA interface only. It still exists, for safety, on the SQL server.

Here is the procedure to remove unused data from all Askia-related databases on your SQL server, with the utility called CcaCleanup.

Pre-requirements:

• The CCA must be stopped during the operation of this tool.
• The new version needs to have VisualC++ redistributable 2010 (vcredist 2010) installed. Depending on the server architecture, you might need a different version of this dependency. They are available on the CCA server, in the \askiafield\Packages\VcRedist\ folder. Use only the vcredist_x86 packs.

CcaCleanup.exe allows you to clean the Statistics, Surveys and Webinterviews databases and is located in the installation folder of CCA: "C:\Program Files (x86)\AskiaField\CCA\". If you don't find it, please download it from here.

It should be started directly from the CCA server.

Below is the procedure for cleaning the databases:

• Populate the two "Source" fields with the connection ADO connection strings.
• For a first run, let the default value for SQL Queries Timeout at 300 seconds. It could be necessary, especially during the Statistics clean up by date, to increase this value.
• Choose the type of data that should be removed.
• Date cleanup: this deletes all records in the Statistics database prior to the date indicated and for all surveys (deleted or not).
• Survey cleanup: deletes all records in the Statistics database for the selected surveys. 
• List DB cleanup: removes all AskList / AskEmail / AskCall tables from the Lists Database. This only deletes data related to lists that have been previously deleted from the CCA/Supervisor interface.
• Survey DB cleanup: removes all tables ASKReponseXXX from the Surveys database referring to studies that have been deleted from the interface of the CCA/Supervisor.
• WebInterviews DB cleanup: removes data from the WebInterviews database for surveys that have been previously deleted from CCA and removed from WebProd.

Tips:

• You can get the connection strings directly from the CCA/Supervisor menu > options > CCA settings in Lists/Statistics/Surveys part of the CCA settings.
• For the WebProd, the connection string for WebInterview is located in WebProd administration pages > configuration > WebProd Configuration > Connection string for interview db. The one for WebProd database is store in the global.asa file (which can be opened with notepad) in "strWebProdDatabase ".

Step 3

Free up hard drive space

You can free up hard drive space on the servers.

There is no officially released script to perform a file clean up yet. On the other hand, here is the default location of files that can be left over after production.

Pre-requirements: 

• Step 1 must be completed beforehand.
• Only remove working directories that have been previously deleted (and optionally backed up) from CCA.

1. The working directory stores unused QES files.
   In the CCA settings, you can find the current path of this directory. In these directories, spot the working directories of the unused surveys, and remove or back them up to an external hard drive location.
   
2. The Logging folder stores old logs that can be zipped/removed. Its path can be found in the logging settings of CCA.