Administering Data › SQL Server Database Maintenance

SQL Server Database Maintenance

The following recommendations can help you maintain your SQL Server database for best performance:

• Verify that SQL Server is taking advantage of available memory. The amount of memory allocated to SQL Server should be the total system memory minus memory required by the operating system and other applications.
• Create a database maintenance plan for your CA Harvest SCM database. For example, you can run integrity checks, update statistics, perform database backups, and rebuild the indexes at prescheduled times. Verify that the backup schedule is set to run regularly, especially after a large amount of data has been added, changed, or deleted.

  Note: For instructions to create a database maintenance plan, see your SQL Server documentation.

• Eventually, the MSDB database may fill up to a point where it can slow down the SQL Server Agent. You can remove backup and restore records from the MSDB database by running the sp_delete_backuphistory stored procedure. For example:

  USE msdb
  EXEC sp_delete_backuphistory '08/28/05'
  

  This example removes records dated earlier than 08/28/05.

• Remember to perform regular disk maintenance so that the physical files on your disks are defragmented. Rebuilding indexes is not the same as defragmenting the drive. Rebuilding indexes occurs in the SQL Server data files only.
• If your sqlserver log gets very large and takes a long time to load, run the command DBCC ERRORLOG to truncate the server log.

Note: Use the information in this section as a guide. For complete instructions to maintain your SQL Server database, see your SQL Server documentation.