AuditWizard - Troubleshooting - Database Maintenance
m (Minor text changes) |
(New section: "Shrinking the SQL Transaction Log") |
||
| (8 intermediate revisions by one user not shown) | |||
| Line 3: | Line 3: | ||
=Overview= | =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. | 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. '''[[AuditWizard - Using AuditWizard - Administration - General - Database Maintenance - Change Database Wizard - SQL Migration|Migrate to SQL Server]]''' at your earliest convenience. | ||
=Back Up the Database= | =Back Up the Database= | ||
| Line 8: | Line 11: | ||
#Open Microsoft SQL Server Management Studio. | #Open Microsoft SQL Server Management Studio. | ||
| − | #Right-click the ‘'AuditWizard'’ database and select ''Tasks > | + | #Right-click the ‘'AuditWizard'’ database and select ''Tasks > Back Up...'' |
| − | #In the Source section, select | + | #In the Source section, select ''Full'' from the ''Backup type'' menu. |
| − | # | + | #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. |
#Click the Options page in the upper-left of the dialog. | #Click the Options page in the upper-left of the dialog. | ||
| − | # | + | #Click the ''Overwrite All Existing Back-up Sets'' radio button. |
| + | #(Optional) Check the option ''Verify backup when finished''. | ||
| + | #Click the OK button to begin the backup process. | ||
| + | |||
| + | =[[AuditWizard™_-_Using_AuditWizard_-_Administration_-_General_-_Database_Maintenance#Database_Purging|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: '''[[AuditWizard™_-_Using_AuditWizard_-_Administration_-_General_-_Database_Maintenance#Database_Purging|Database Purging]]''' | ||
| − | = | + | =Shrinking the SQL Transaction Log= |
| − | + | The SQL transaction log is managed by SQL Server. If it becomes large, refer to '''[http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx this article]''' for instructions on how to shrink it. | |
| − | + | =Truncate Logs= | |
| + | ==SQL Server Versions SQL Server 2008 or Later== | ||
| + | The <code>backup log Auditwizard with no_log</code> method '''[https://msdn.microsoft.com/library/ms144262(v=sql.100) has been deprecated in SQL Server 2008]'''. The alternative option provided is to '''[https://msdn.microsoft.com/en-in/library/ms189275(v=sql.110).aspx set Recovery Models]''' in the database Properties. Consult your database admin for assistance in '''[https://technet.microsoft.com/en-us/library/ms175987(v=sql.105).aspx 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. | Open Query Analyzer from within SQL Management Studio. | ||
| Line 36: | Line 48: | ||
=Restart SQL Server= | =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 | + | 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. |
<p align="right">[[File:btn_back_up.png|link=User Guide for AuditWizard]] [[User Guide for AuditWizard|<u>Back to Contents</u>]]</p> | <p align="right">[[File:btn_back_up.png|link=User Guide for AuditWizard]] [[User Guide for AuditWizard|<u>Back to Contents</u>]]</p> | ||
Latest revision as of 03:12, 8 March 2017
Contents |
[edit] 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.
[edit] 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.
[edit] 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.
- Open Microsoft SQL Server Management Studio.
- Right-click the ‘'AuditWizard'’ database and select Tasks > Back Up...
- In the Source section, select Full from the Backup type menu.
- 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.
- Click the Options page in the upper-left of the dialog.
- Click the Overwrite All Existing Back-up Sets radio button.
- (Optional) Check the option Verify backup when finished.
- Click the OK button to begin the backup process.
[edit] 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
[edit] 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.
[edit] Truncate Logs
[edit] 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.
[edit] 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)
[edit] 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.
