You are previewing Brad's Sure Guide to SQL Server Maintenance Plans.
O'Reilly logo
Brad's Sure Guide to SQL Server Maintenance Plans

Book Description

In his new book, Brad's Sure Guide to SQL Server Maintenance Plans, Brad McGehee takes you on a comprehensive tour of two SQL Server tools designed to help DBAs ensure that a "required minimum" level of maintenance takes place on their SQL Server instances: the Maintenance Plan Wizard and the Maintenance Plan Designer.

Table of Contents

  1. Copyright
  2. About the Author
  3. Acknowledgements
  4. Introduction
    1. Who Should Read this Book?
    2. Goals of this Book
    3. SQL Server Editions Covered in this Book
  5. 1. Why is Database Maintenance Important?
    1. The Scope of Database Maintenance
    2. Different Approaches to Database Maintenance
      1. Maintenance Plan Wizard
      2. Maintenance Plan Designer
      3. T-SQL Scripts
      4. PowerShell Scripts
    3. Core Maintenance Plan Tasks
      1. Back up Databases
      2. Verify the Integrity of a Database
      3. Maintain a Database's Indexes
      4. Maintain Index and Column Statistics
      5. Remove Older Data from msdb
      6. Remove Old Backups
    4. What's Outside the Scope of the Maintenance Plan Wizard and Designer?
    5. Summary
  6. 2. Before You Create any Maintenance Plans...
    1. How to Configure Database Mail
    2. How to Configure a SQL Server Agent Operator
    3. Summary
  7. 3. Getting Started with the Maintenance Plan Wizard
    1. Exploiting the Full Potential of the Wizard
    2. Investigating Existing Maintenance Plans
    3. Creating a Maintenance Plan
      1. Starting the Maintenance Plan Wizard
      2. Scheduling Maintenance Tasks
        1. Single Schedule or No Schedule
        2. Separate Schedules for Each Task
      3. Overview of Maintenance Tasks
        1. Check Database Integrity
        2. Shrink Database
        3. Reorganize Index
        4. Rebuild Index
        5. Update Statistics
        6. History Cleanup
        7. Execute SQL Server Agent Job
        8. Back Up Database (Full)
        9. Back Up Database (Differential)
        10. Back Up Database (Transaction Log)
        11. Maintenance Cleanup Task
      4. Selecting Core Maintenance Tasks
      5. Maintenance Task Order
      6. Configuring Individual Tasks
        1. Database Selection
          1. All databases
          2. System databases
          3. All user databases...
          4. These databases
          5. Ignore databases where the state is not online
        2. Task-Specific Configuration Options
        3. Task Scheduling
      7. Report Options
      8. Completing the Wizard
    4. A Closer Look at Maintenance Plan Implementation
    5. Testing Your Maintenance Plan
    6. Summary
  8. 4. Task Scheduling
    1. Scheduling: General Considerations
      1. Avoid Scheduling Tasks During Busy Periods
      2. Avoid Overlapping Tasks
      3. Task Frequency
    2. Task Scheduling in the Wizard
      1. Job Schedule Properties
        1. Job Schedule Name and Type
        2. Job Frequency
        3. Daily Frequency
        4. Job Duration
    3. Scheduling Individual Maintenance Tasks
    4. Summary
  9. 5. Check Database Integrity Task
    1. An Overview of the Check Database Integrity Task
    2. When and How Often to Run Integrity Checks
    3. Configuring the Task
      1. The Include Indexes Option
      2. Creating the Job Schedule
    4. Summary
  10. 6. Shrink Database Task
    1. Sizing Your Database Files
    2. Problems with the Shrink Database Task
    3. The Right Way to Shrink a Database
    4. Summary
  11. 7. Rebuild Index Task
    1. An Overview of the Rebuild Index Task
    2. When and How Often to Rebuild Indexes
      1. Tracking Index Fragmentation
      2. Offline Index Maintenance
      3. Online Index Maintenance
      4. Scripting Index Rebuilds
    3. Configuring the Rebuild Index Task
      1. Database Selection
        1. Selecting Several Databases
        2. Selecting a Specific Database
      2. Free Space Options
      3. Advanced Options
      4. Creating the Job Schedule
    4. Summary
  12. 8. Reorganize Index Task
    1. An Overview of the Reorganize Index Task
    2. Reorganize versus Rebuild
    3. When and How Often to Reorganize Indexes
    4. Configuring the Reorganize Index Task
      1. Database Selection
      2. Compact Large Objects
      3. Creating the Job Schedule
    5. Summary
  13. 9. Update Statistics Task
    1. Overview of the Update Statistics Task
    2. When and How Often to Update Statistics
    3. Configuring the Update Statistics Task
      1. Database Selection
      2. The Update Option
      3. The Scan Type Option
      4. Creating the Job Schedule
    4. Summary
  14. 10. Execute SQL Server Agent Job Task
    1. An Overview of the Execute SQL Server Agent Job Task
    2. When and How Often to Run the Custom Job
    3. Creating SQL Server Agent Jobs
    4. Configuring the Execute SQL Server Agent Job Task
      1. Selecting the Job
      2. Creating the Job Schedule
    5. Summary
  15. 11. History Cleanup Task
    1. An Overview of the History Cleanup Task
    2. When and How Often to Clean Up msdb
    3. Configuring the History Cleanup Task
      1. Selecting the Historical Data to Delete
      2. Creating the Job Schedule
    4. Summary
  16. 12. Back Up Database (Full) Task
    1. Backup Strategy – a Brief Primer
    2. An Overview of the Backup Database (Full) task
    3. When and How Often to Perform Full Backups
    4. Configuring the Back Up Database (Full) Task
      1. Database and Backup Component Selection
      2. Backup File Storage
        1. Back Up Databases Across One or More Files
        2. Create a Backup File for Every Database
          1. Create a sub-directory for each database
          2. Folder
          3. Backup file extension
      3. Verify Backup Integrity
      4. Set Backup Compression
      5. Creating the Job Schedule
    5. Summary
  17. 13. Back Up Database (Differential) Task
    1. An Overview of the Back Up Database (Differential) Task
    2. When and How Often to Perform Differential Backups
    3. Configuring the Back Up Database (Differential) Task
      1. Database and Backup Component Selection
      2. Creating the Job Schedule
    4. Summary
  18. 14. Back Up Database (Transaction Log) Task
    1. An Overview of the Backup Database (Transaction Log) Task
    2. When and How Often to Back Up Transaction Logs
    3. Configuring the Backup Database (Transaction Log) Task
      1. Backing Up the Tail of the Log
      2. Creating the Job Schedule
    4. Summary
  19. 15. Maintenance Cleanup Task
    1. An Overview of the Maintenance Cleanup Task
    2. When and How Often to Clean Up Your Backup and Report Files
    3. Configuring the Maintenance Cleanup Task
      1. Specifying the Type of File to Delete
      2. Specifying File Location
      3. Delete Files Older Than...
      4. Creating the Job Schedule
    4. Summary
  20. 16. Introduction to the Maintenance Plan Designer
    1. Features Unique to the Maintenance Plan Designer
    2. Starting the Maintenance Plan Designer
    3. Exploring the Maintenance Plan Designer
      1. Object Explorer
      2. Maintenance Task Toolbox
      3. Subplans and the Design Surface
        1. Subplans
        2. The Design Surface
          1. Dragging and dropping tasks
          2. Task configuration boxes
      4. Designer Menu bar
        1. Add Subplan
        2. Subplan Properties
        3. Delete Selected Subplan
        4. Subplan Schedule
        5. Remove Schedule
        6. Manage Connections
        7. Reporting and Logging
        8. MultiServer Maintenance Plans
    4. Summary
  21. 17. Configuring Maintenance Tasks Using the Designer
    1. A Note of Drag-and-Drop Caution
    2. Check Database Integrity Task
    3. Rebuild Index Task
    4. Reorganize Index Task
    5. Update Statistics Task
    6. Shrink Database Task
    7. Execute SQL Server Agent Job Task
    8. History Cleanup Task
    9. Maintenance Cleanup Task
    10. Back Up Database Task
    11. Execute T-SQL Statement Task
    12. Notify Operator Task
    13. Summary
  22. 18. Subplans and Precedence
    1. Subplans
      1. Using a Single Subplan: Pros and Cons
      2. Using Multiple Subplans: Pros and Cons
      3. Using Subplans
    2. How to Use Precedence
    3. Summary
  23. 19. Create and Modify Maintenance Plans Using the Designer
    1. Establishing Your Maintenance Goals
    2. Creating Maintenance Plans: the Big Picture
      1. Create the New Maintenance Plan
      2. Create the Subplans
      3. Add the Maintenance Plan Tasks
        1. Daily Full Backup Subplan
        2. Hourly Log Backup Subplan
        3. Weekly Maintenance Subplan
      4. Configure the Maintenance Plan Tasks
      5. Set Precedence
        1. Daily Full Backup Subplan
        2. Hourly Log Backup Subplan
        3. Weekly Maintenance Subplan
      6. Define Reporting and Logging
      7. Save the Maintenance Plan
      8. Test the Maintenance Plan
      9. Set the Schedules
      10. Run in Production and Follow Up
    3. Modifying an Existing Maintenance Plan
    4. Summary