SQL Server Maintenance Plans
      Back to home
      1. Customer Help Center
      2. SQL Server Maintenance Plans

      SQL Server Maintenance Plans

      SQL Server Maintenance Plans Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies

      Administration 

      1. Monitor database performance 
      2. Monitor disk utilization 
      3. Delete any database objects that are no longer needed, such as maintenance plans and indexes.
      4. Ensure that maintenance plans have notifications enabled and configured 

      Maintenance Actions 

      1. Check database integrity 
        1. DBCC CHECKDB 
        2. This step confirms verifies that the database does not contain corruption and will fix minor issues when they are found.   
        3. This command checks the consistency of disk allocation, the integrity of pages and structures of tables and indexes, and the consistency of the database catalog. 
        4. This step will reduce the risk of a corrupted database being written to backups and the backup overwritten with corruption.  It should be run in concert with the backup schedule.  If weekly full backups are run, this should be run no less than 1x per week. 
        5. Three options are available for corruption repair and can be specified as part of the configuration; Repair allow data loss, repair fast, repair rebuild. 
        6. More details:  https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver16 
      2. Rebuild / Reorganize / Defragmenting indexes 
        1. Managing the indexes within a database will allow the database to perform within the designed performance.  Indexes will become fragmented or lose their effectiveness over time if they are not maintained. 
        2. If fragmentation on an index is < 10%, no action is required. 
        3. If fragmentation on an index is between 10-30%, It is recommended that indexes be defragmented. 
        4. Indexes that are more than 30% fragmented should be rebuilt. 
      3. Rebuild Statistics 
        1. SQL Server only updates index specific stats after an index is rebuilt.   
        2. To fully update the statistics which the query optimizer uses, a separate command sp_updatestats should be run at the conclusion of the index rebuild / reorganize / defrag operation. 
        3. This can be configured in the SQL maintenance plan and should be done after the indexes have been updated. 
      4. History Clean up 
        1. This step removes historical log entries that will grow over time. 
        2. This action ensures that the log of maintenance activities do not slow down the actual maintenance being performed. 
      5. Shrink Files 
        1. The data file should only be shrunk after an action that creates a significant amount of free space in the database.  Doing this will create additional fragmentation that will require additional clean up. 
        2. The log file can be shrunk more often once the data in the file has been backed up. 
        3. Opinions vary on the appropriate timing for shrinking a database. On Engineering support options , it is best to manually shrink the database as necessary, rather than incorporating it into a maintenance plan.
      6.   Backups 
      1. A combination of full, differential, and transaction log backups should be performed on each database.  The schedule for each would depend on the size of the server, the tolerance for data loss, and the skillset of the user managing the backups. 
      2. Backups should be written to a separate physical drive as the database. 
      3. Two proposed schedules 
      4. Monthly full, Daily differential, Transaction log every 15 minutes 
      5. Weekly full, Daily differential, Transaction log every 15 minutes 
      6. It is recommended that at least 2-3 intervals (Monthly or Weekly) of the full backup be maintained for recovery as needed.  Also, backups should be frequently tested to ensure that they have not become corrupted, and the system can be restored in the event of a catastrophe. 
      7. The backup history stored in the database log should be maintained at the same frequency as backups on disk are stored. 

      Suggested Maintenance Plans 

       

      Plan 1 – Proactive Maintenance 

      • Suggested that this be run weekly at a set time (e.g. – Sundays at 12p ET) with error notification sent to a monitored mailbox. 
      • Actions 
        • Check database integrity 
        • Rebuild / Reorganize / Defragmenting indexes 
        • Rebuild Statistics 
        • History Cleanup 

      Plan 2 – Full Backup 

      • Suggested that this be run daily at a set time (e.g. – 1pm ET) with error notification sent to a monitored mailbox. 
      • Suggested Options 
        • Backup file extension (bak) 
        • Backup should be compressed 
        • Backup set should expire after {X} days.  Setting this will determine how long the knowledge of the backup is maintained in the database logs. 
        • Verify Backup Integrity 
        • Optional:  Encrypt Backup 

      Plan 4 – Incremental Backup 

      • Scheduled to run daily, every 15 minutes with error notification set to a monitored mailbox. 
      • Suggested Options 
        • Backup file extension (trn) 
        • Backup should be compressed 
        • Backup set should expire after {X} days.  Setting this will determine how long the knowledge of the backup is maintained in the database logs. 
        • Verify Backup Integrity 
        • Optional:  Encrypt Backup