Revision as of 23:03, 4 September 2013

Back to Contents

Overview

This document describes a few operations we recommend to improve or maintain the performance of AuditWizard v8 and the back-end SQL server. It does not apply to the built-in (SQL Compact) database. These steps should allow for optimal upload, application and reporting performance. Follow these steps in sequence.

Back Up the Database

We highly recommend a back up of the database before continuing to be able to recover from any damage that can possibly occur in the event of an error. A back-up can only be done if you have migrated away from the default SQL Compact Edition.

1. Open Microsoft SQL Server Management Studio.
2. Right-click the ‘'AuditWizard'’ database and select Tasks > Backup
3. In the Source section, select ‘'Full' from the Backup type menu.
4. Choose a destination path and file name. If you intend to keep archives of the database, you may want to give the backup set a naming scheme with numbers or dates to show when it was backed up.
5. Click the Options page in the upper-left of the dialog.
6. Select Overwrite All Existing Back-up Sets'’.

Purge Database and Audit Files

Reference: Database Purging

Open Audit Wizard, and then go to Administration > General > Database Maintenance Menu. The Audit history accounts for every audit that has been done from the time that auditing began in your environment or since the last purge. Audit History tracks all changes that have been made since the last audit. For users that are auditing every 15-30 days, you may want to perform this purge at least quarterly (both histories). For users that audit on 0-14 days, we recommend you purge both histories monthly, as this will reduce the overhead on the database. The backup performed in the previous step can always be archived to keep a record of all information at that time.

Truncate the Logs with a SQL Query

Open Query Analyzer from within SQL Management Studio.

In the blank part of the Query window, type in the following commands one at a time, then execute individually by pressing F5 or clicking the red Execute button on the tool bar. This command doesn't actually do a backup—it just clears out the log but stays at its original size.

   backup log Auditwizard with no_log

The following command reduces the physical file size down to match its contents. The 10 parameter is to tell it what rule to use for expanding it again (i.e. increase by 10% whenever it's full) This will shrink the overall database size:

   DBCC SHRINKDATABASE (AuditWizard, 10)

If for some reason these commands do not have any major effect on the log files (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AuditWizard.mdf and AuditWizard_log.LDF), then try running these commands to shrink the files directly (where X is size of file in megabytes):

   dbcc shrinkfile ('Auditwizard_log',X)
   dbcc shrinkfile ('AuditWizard',X)

Restart SQL Server

We have seen some cases where the SQL server process may occupy a good deal of physical memory after an upgrade or upload of data, or even perhaps from other databases that may reside on the same server. We recommend starting and stopping the SQL Server services, or even a reboot of the server is also a good idea to make sure the resources are refreshed. This should ensure that there is enough memory available to the SQL server process to run the uploads or reports, as we have seen in some cases where there is a audit getting uploaded and there is not more physical memory available to perform the action, which leads to much slower performance.

Back to Contents