This page handles some database settings which might improve the performance of the AskiaField and prevent some problems arising with heavy database usage.
Note: These document describes the actions to take in SQL Server 2008 R2. Some these features might not be available on older versions of SQL Server, or might require different actions. Please read that products manual for specific details on how to handle this.

Database size and Autogrowth

Right-click Database -> properties -> Files
Set initial size for Data and log to acceptable minimums:
- Data: 100 MB
- Log: 10 MB/100 MB (depending on recovery model)
This will ensure the database doesn't have to be resized immediately.

Set Autogrowth to a value in megabytes (not in percentage)
- Data: 25 MB
- Log: 5 MB/25 MB (depending on recovery model)
This will ensure that the database files don't have to be resized too many times. Also, by setting the Autogrowth to a value in megabytes, problems for large databases are prevented: When the database is very large (eg: 2 GB) and configured to grow by percentage (eg: 10%), the database file will be locked for a long time while the database is enlarging.

Recovery model

Right-click Database -> properties -> Options (recovery model box).

Backup and restore operations occur within the context of recovery models. A recovery model is a database property that controls the basic behaviour of backup and restore operations for a database. For instance, a recovery model controls how transactions are logged, whether the transaction log requires backing up, and what kinds of restore operations are available.

Select the desired recovery models are possible:

• Simple recovery model: This model minimally logs most transactions, logging only the information required to ensure database consistency after a system crash or after restoring a data backup. As old transactions are committed and the log is not needed anymore, the log is truncated. This truncation of the log eliminates backing up and restoring transaction logs. This simplification comes at the expense, however, of potential data loss in the event of a disaster. Without log backups, the database is recoverable only up to the time of its most recent data backup, which is a consideration if you are using the Enterprise Edition of SQL Server.
• Full recovery model: This model fully logs all transactions and retains all the transaction log records until after they are backed up. In the Enterprise Edition of SQL Server, the full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log has been backed up after the failure. Bulk-logged recovery model: This model minimally logs most bulk operations, such as index creation and bulk loads, while fully logging other transactions.
• Bulk-logged recovery increases performance for bulk operations and is intended to be used a supplement to the full recovery model. The bulk-logged recovery model supports all forms of recovery, though with some restrictions.

To prevent very huge transaction logs and the possible resulting problems, you can set the recovery model to 'Simple'. In that case the client has to implement additional backup scenarios to prevent data loss in the case of system failures.

Shrink database

Right-click Database -> Tasks -> Shrink -> Database -> OK
This will shrink the database and transaction log to use the new settings.

Recommendations

The following parameters are recommended for any fresh AskiaField installation

Dedicated to the CCA:

• Askia_CCA (Initial size: 100Mb; Auto growth: 100Mb)
• Askia_Global (Initial size: 10Mb; Auto growth: 10Mb)
• Askia_Lists (Initial size: 1Gb; Auto growth: 1Gb)
• Askia_Statistics (Initial size: 1Gb; Auto growth: 1Gb)
• Askia_Surveys (Initial size: 1Gb; Auto growth: 1Gb)

Dedicated to AskiaWeb:

• Askia_Webprod (Initial size: 10Mb; Auto growth: 10Mb)
• Askia_WebInterviews (Initial size: 1Gb; Auto growth: 1Gb)

Recommended default options to set on the DBs.

• Collation: SQL_Latin1_General_CP1_CI_AS
• Recovery model: Simple
• Auto Close: False
• Auto Create Statistics: False
• Auto Shrink: False
• Auto Update Statistics: False
• Auto Update Statistics Asynchronously: False

The SQL user account to be used for the Askia applications should have the following details.

• “Owner” of the SQL DBs listed above
• “Securityadmin” & “Setupadmin” roles