Tips & Tricks - Manufacturing Quality Control Software - Because your profit is on the bottom line...

Tips & Tricks

Routine database maintenance enhances WinSPC performance


Find out how to keep WinSPC operating at peak performance by periodically re-indexing your WinSPC database. Below are step-by-step instructions for setting up a scheduled procedure to do this.

If you are not able to administer the database, pass this along to the IT staff responsible for routine maintenance. The frequency of maintenance is dependent upon how much data you collect. Start by tracking the size of your database to determine a schedule that's right for you. Then, consider setting up a scheduled procedure based on your usage scenario. Additionally, DataNet recommends purging the transaction log once per month.

Archiving older data also improves system performance. Work with your database administrator or consult your database reference manuals for instructions. This preventative maintenance will improve the efficiency of your database, speed up response times and minimize software failures.

Maintaining your WINSPC database

Databases require maintenance. Whether your WinSPC implementation consists of multiple WinSPC stations networked to a higher-end database or a single computer running both WinSPC and a lower-end database, there are maintenance tasks, sometimes referred to as tuning tasks, which should be completed on a regular basis to ensure the proper functioning of your database.

Large networked implementations are usually completed in consultation with an organization’s IT personnel who are familiar with the requirements of database maintenance. Occasionally, small networked implementations and single-computer implementations are completed independently of IT personnel with database expertise.

Non-IT personnel responsible for small networked or single-computer implementations are encouraged to contact their IT department for assistance in maintaining their database and, in addition, consult the guides and help documentation accompanying the database. For those circumstances in which assistance and documentation are not available, the following brief guidelines are offered as an aid.

These guidelines apply specifically to Microsoft Corporation’s SQL Server 2000 database. If you are using another database, although the general concepts remain relevant, these guidelines will need to be adapted to the database in use.

1. Open SQL Server Enterprise Manager.

2. Right-click the WinSPC database and select All TasksMaintenance Plan. This launches the Database Maintenance Plan Wizard. This wizard will guide you through the steps that are required to keep your database maintained. (Note: The steps that follow use many defaults. At your discretion, depending on the needs of your implementation, these defaults may be overridden.)

3. On the first dialog of the Database Maintenance Plan Wizard, the Welcome dialog, read the text and click the Next button.

4. On the Select Databases dialog:
  • Select These databases if it is not already selected.
  • In the Database list, check the WinSPC database check box if it is not already checked.
  • Click the Next button.

5. Complete the Update Data Optimization Information dialog as desired and click the Next button. As a suggestion:
  • Check the Reorganize data and index pages check box.
  • Select the Reorganize pages with the original amount of free space option.
  • Check the Remove unused space from database files check box.
  • Enter 100 MB in the When it grows beyond text box.
  • Enter 25 % for the Amount of free space to remain after shrink.
  • Click the Change button and, in the Edit Recurring Job Schedule that appears:
    • Check the Enable schedule check box.
    • At Occurs, select Weekly.
    • Under Weekly, at Every____week(s) on, specify 1 and check the Sun check box.
    • Under Daily frequency, select Occurs once at and specify 2:00:00 AM in the space provided.
    • Under Duration, specify a Start date of the next Sunday and select the No end date option.
    • Click the OK button.

6. Complete the Database Integrity Check dialog as desired and click the Next button. As a suggestion:
  • Check the Check database integrity check box.
  • Select the Include indexes option.
  • Check the Attempt to repair any minor problems check box if you want the SQL Server Enterprise Manager on its own to attempt a fix of the minor issues it finds.
  • Check the Perform these checks before doing backups check box.
  • Click the Change button and, as above, complete the Edit Recurring Job Schedule dialog.

7. Complete the Specify the Database Backup Plan dialog as desired and click the Next button.
  • Check the Back up the database as part of the maintenance check box.
  • Check the Verify the integrity of the backup when complete check box.
  • At Location to store the backup file, select Disk.
  • Click the Change... button and, on the associated dialog, define a schedule for database backups.
  • Click the Change button and, as above, complete the Edit Recurring Job Schedule dialog.

8. Complete the Specify Backup Disk Directory dialog as desired and click the Next button. As a suggestion:
  • At Directory in which to store the backup file, select Use the default backup directory.
  • At Backup file extension, enter BAK.

9. Complete the Specify the Transaction Log Backup Plan dialog as desired and click the Next button.

10. Complete the Reports to Generate dialog as desired and click the Next button.

11. Complete the Maintenance Plan History dialog as desired and click the Next button. As a suggestion:
  • Check the Write history to the msdb.dba.sysdbmaintplan checkbox.
  • Check the Limit rows in the table to check box and enter 1000 in the space provided.

12. On the Maintenance Plan Summary dialog, enter a Plan name and review the plan specifics. If you are not satisfied with the selections, click the Back button and modify them until you are satisfied. Then, click the Finish button.

13. Click Finish.

14. If a message indicating the SQL server agent service in Windows services is not running, contact a Windows administrator in your company who can start it.
 
[ Back ]
Quality Control Products SPC Software Applications Why Use WinSPC? SPC Information