Database Maintenance

Keep your HelpMaster Database running safely and efficiently

Database Maintenance

It is recommended that the HelpMaster database is regularly maintained by a qualified, SQL Server administrator. Best practice procedures include regular backup, data integrity checking, query optimization and indexing.  When the HelpMaster database is created, a maintenance plan is not created with it.  This will need to be implemented by your database administrator.

For further information about database maintenance, please consult SQL Server Books Online.  A search on Database maintenance or maintenance plans will prove helpful.

Additional SQL Server resources are available from Microsoft at www.microsoft.com/sql

See Also

http://msdn.microsoft.com/en-us/library/ms187658.aspx

Why is database maintenance important?

A regularly maintained database will perform better, occupy less space on your server hard-drive, and have less internal errors and inconsistencies.  In the event of catastrophic hardware failure, database crashes, or system malfunctions etc, having a recent database backup is essential.

Use

Below is a simplified example of a maintenance plan that might be appropriate for your environment.  It is highly recommended that you perform you own research to determine which database maintenance plan is suitable for your environment.

  1. From SQL Server Management Studio, expand Management, right-click on Maintenance Plans and select New Maintenance Plan…

  2. Give the plan a name!

  3. From the Toolbox, drag and drop maintenance tasks onto the sub-plan to build up your plan.  For a detailed discussion on each of the maintenance tasks and the suggested order in which they should be arranged, please refer to the Microsoft SQL Server documentation.  http://msdn.microsoft.com/en-us/library/ms140255.aspx

  4. Right-click on the task and select Edit… to configure the task to your requirements.

  5. Connect each task to each other by dragging the arrows onto the next task to be executed

Reduce Transaction log file size via a “Simple” Database Backup

SQL Server backup is a big topic and is outside the scope of this documentation (See Microsoft SQL Backup docs here), however one quick way to potentially reduce file size (and perform a backup at the same time), is to set the database backup mode to “Simple”, and then do a full backup. This will truncate the database log file as part of the database backup process.

Don’t forget to switch it back to the usual backup type!

SQL Server Simple Database backup

See Also

System Administration - Database settings (including maintenance options)

Database and Reports Wizard

Database growth

Hardware and Software Requirements - Microsoft SQL Server