SQL Server 2008 R2 : Database Maintenance - Executing a Maintenance Plan

5/19/2013 7:27:34 PM

Executing a Maintenance Plan

Maintenance plans that have been scheduled run automatically according to the schedule defined. You can also run maintenance plans manually by right-clicking a maintenance plan and selecting Execute or by selecting the SQL Server Agent job associated with the maintenance plan and starting the job. The execution behavior is different, depending on the means you use. If you choose to run the maintenance plan from the Management node, the SSIS package is launched, and the Execute Maintenance Plan window displays the current status of the plan execution.

If you run the SQL Server Agent job to execute the maintenance plan, a dialog box indicating the execution status of the job appears. The dialog does not indicate success for the maintenance plan until the entire maintenance plan has completed. The dialog box for the job can be closed, and the job will still continue to run. The Execute Maintenance Plan window, on the other hand, does not have an option to close it, and it must stay open until the plan completes.

There are two other means for monitoring the execution of maintenance plans. The Job Activity Monitor shows a status of executing while a maintenance plan is executing. You can set the refresh settings on the Job Activity Monitor to auto-refresh for the desired increment. You can also monitor the execution by establishing a connection to the SSIS server in SSMS. To establish an SSIS connection in SSMS, you click the Connect drop-down in the Object Explorer and choose Integration Services. Figure 1 shows an example of the Object Explorer with an Integration Services connection.

Figure 1. The Object Explorer with an Integration Services connection.

The Integration Services connection in the Object Explorer shows the packages that are running in addition to the packages that have been created. If you expand the Stored Packages node and navigate to the MSDB node, you see a node named Maintenance Plans that shows all the SSIS packages that have been created. 


SSIS does not need to be installed on the SQL Server machine to be able to create and execute maintenance plans. In the initial release of SQL Server 2005, this was a requirement but was changed with SQL Server 2005 SP2. This change carried over to SQL Server 2008, and maintenance plans are now fully functional with the SQL Server Database Services installation.

Maintenance Without a Maintenance Plan

You can perform database maintenance without the use of the built-in maintenance plans that come with SQL Server. The additional complexity in SQL Server 2008 may steer some people away from the use of these plans. In addition, these plans cannot be scripted, so deployment to multiple environments is not straightforward.

Database maintenance that is performed without a maintenance plan is often performed using custom scripts or stored procedures that execute the T-SQL commands to perform the maintenance. Other methods include manually executing the SQLMAINT utility to perform various maintenance tasks such as database backups and consistency checks. Often these maintenance commands or custom scripts are then scheduled to run on a regular basis by manually setting up jobs within the SQL Server Agent job scheduler in SQL Server Management Studio. 

Setting up maintenance tasks manually is a viable option, especially for the more experienced DBA because it requires additional development work and familiarity with the maintenance commands and options. However, even the experienced DBA should consider using maintenance plans because maintenance tasks set up manually may lack the integration with other SQL Server components that is offered with the SQL Server 2008 maintenance plans.

Database Maintenance Policies

Policy-Based Management, a new management feature introduced in SQL Server 2008, allows you to manage your SQL Server instances through clearly defined policies, reducing the potential for administrative errors or oversight. The policy-based framework implements the policies you defined via a Policy Engine, SQL Server Agent jobs, SQLCLR, DDL triggers, and Service Broker. You can choose to have the policies you defined be applied or evaluated against a single server or a group of servers, thus improving the scalability of monitoring and administration.

Policy-Based Management allows you to prescribe the way you want your databases maintained, and the system will help ensure things stay that way. Essentially, Policy-Based Management allows you to define rules for one or more SQL Servers and evaluate them. The goal of this feature is to make it easier for you to manage one or more servers by notifying you when servers are out of compliance with the database maintenance policies you have defined.

For example, you could define a policy to ensure that transaction log backups are being performed on the appropriate intervals on your OLTP databases. Policy-Based Management allows you to determine when one of your databases is not in compliance with your log backup policy. You can set up this policy to be evaluated on demand or via a schedule.

  •  SQL Server 2008 R2 : Database Maintenance - Managing Maintenance Plans Without the Wizard
  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 2)
  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 1)
  •  SQL Server 2008 : Common performance problems (part 2)
  •  SQL Server 2008 : Common performance problems (part 1) - Procedure cache bloating
  •  SQL Server 2008 : SQLOS schedulers, Wait analysis
  •  MySQL for Python : Creating Users and Granting Access - Removing privileges in MySQL, Using REVOKE in Python
  •  MySQL for Python : Creating Users and Granting Access - GRANT access in MySQL
  •  MySQL for Python : Creating Users and Granting Access - Removing users in MySQL, DROPping users in Python, Granting access in Python
  •  MySQL for Python : Creating Users and Granting Access - Creating users from Python
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone