Microsoft SQL Server MDB Considerations › Microsoft SQL Server Maintenance

Microsoft SQL Server Maintenance

Extended updates to the MDB will eventually cause reduced Microsoft SQL Server performance. Microsoft SQL Server should receive the following periodic maintenance:

• Monitor indexes - Fragmented indexes should be rebuilt using Microsoft SQL Server facilities.
• Monitor data files - Operating system data files should be de-fragmented as necessary.
• Monitor the transaction log - The size of the transaction log automatically expands and shrinks. For best performance:
  • Increase the initial size of the transaction log file based on estimated usage.
  • Use a specific growth increment amount such as 100 Megabytes, instead of a percentage increment.
  • Disable automatic transaction log file shrinkage and manually shrink the transaction log files as determined from your monitoring efforts.
  • De-fragment the transaction log file as necessary.
• Monitoring disk space - Disk space should be kept at least 20% free.
• Backup and restore - The following should be included in your backup and recovery plan:
  • Database and transaction log files should be scheduled for regular backups.
  • The master database should be included in the backup plan.
  • Multiple simultaneous backup devices may be used to improve performance.

For More Information, see Microsoft SQL Server Books Online (Database Maintenance Plan Wizard, DBCC SHOWCONTIG, and DBCC_INDEXDEFRAG).