AuditWizard - Troubleshooting - Database Maintenance

Back to Contents

Overview

This article 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.

Migrate Database from SQL Compact to SQL Server

We don't recommend that you run AuditWizard from the built-in SQL Compact database indefinitely. Its purpose is to allow for immediate use of the application, but migrating to SQL Server gives many important benefits such as backing up. Migrate to SQL Server at your earliest convenience.

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 > Back Up...
3. In the Source section, select Full from the Backup type menu.
4. If you don't want to use the default path and file name, click the Add... button and choose a different 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. Click the Overwrite All Existing Back-up Sets radio button.
7. (Optional) Check the option Verify backup when finished.
8. Click the OK button to begin the backup process.

Purge Database and Audit Files

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 every 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.

Procedure: Database Purging

Shrinking the SQL Transaction Log

The SQL transaction log is managed by SQL Server. If it becomes large, refer to this article for instructions on how to shrink it.

Truncate Logs

SQL Server Versions SQL Server 2008 or Later

The backup log Auditwizard with no_log method has been deprecated in SQL Server 2008. The alternative option provided is to set Recovery Models in the database Properties. Consult your database admin for assistance in choosing the appropriate Recovery Model. Most likely it would be changed from Simple to Full.

SQL Server Versions Earlier than SQL Server 2008

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 in which an audit is being uploaded and there is not enough physical memory available to perform the action.

Back to Contents