Database Maintenance Plan Optimizations, Integrity, Free Space

• HookSQLDBA7

  SSChampion

  Points: 10709

  More actions

  November 22, 2004 at 2:00 pm

  #182953

  I am in the process of setting up a Database Maintenance Plan for our User Databases. I am a little confused by the Optimization Tab Options and the Integrity Tab Options. On the Optimization Tab I would like to select 'Reorganize data and index pages' and 'Reorganize pages with the original amount of free space'. How may I determine the 'original amount of free space'?

  What is a general guideline to follow for the Optimization Tab. For example, the 'Reorganize data and index pages', 'Update the statistics used by the query optimizer' and 'Remove unused space from database file'?

  Also, should the schedule for the Optimization Tab and the Integrity Tab be at the same time? Should the Optimization Tab's Tasks start before the Integrity Tab's Tasks or vice-versa?

  I am using SQL Server 2000 (SP3). We have 2 Databases (10 GBs and 20 GBs). There are probably about 15,000 records loaded into each database Monday thru Friday. I would like to schedule the Database Maintenance Jobs on Sunday Night.  

  I would also like to set up a Database Maintenance Plan for our System Databases. Should this plan follow the same guideline as the plan for the User Databases?

  Thanks in advance, Kevin

   

   

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736280

  More actions

  November 22, 2004 at 5:27 pm

  #531148

  I typically run optimizations once a week and integrity before all backups.

  I'd run integrity, then optimizations.