Create a Maintenance Plan

Article
06/13/2017

This topic describes how to create a single server or multiserver maintenance plan in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. Using Management Studio, you can create these maintenance plans in one of two ways: by either using the Maintenance Plan Wizard or the design surface. The Wizard is best for creating basic maintenance plans, while creating a plan using the design surface allows you to utilize enhanced workflow.

In This Topic

Before you begin:

Limitations and Restrictions

Security
To create a maintenance plan, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

Limitations and Restrictions

To create a multiserver maintenance plan, a multiserver environment containing one master server and one or more target servers must be configured. Multiserver maintenance plans must be created and maintained on the master server. These plans can be viewed, but not maintained, on target servers.

Security

Permissions

To create or manage Maintenance Plans, you must be a member of the sysadmin fixed server role.

Using SQL Server Management Studio

To create a maintenance plan using the Maintenance Plan Wizard

In Object Explorer, click the plus sign to expand the server where you want to create a maintenance plan.
Click the plus sign to expand the Management folder.
Right-click the Maintenance Plans folder and select Maintenance Plan Wizard.
Follow the steps of the wizard to create a maintenance plan. For more information, see Use the Maintenance Plan Wizard.

To create a maintenance plan using the design surface

In Object Explorer, click the plus sign to expand the server where you want to create a maintenance plan.
Click the plus sign to expand the Management folder.
Right-click the Maintenance Plans folder and select New Maintenance Plan.
Create a maintenance plan following the steps in Create a Maintenance Plan (Maintenance Plan Design Surface).

Using Transact-SQL

To create a maintenance plan

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.

Copy and paste the following example into the query window and click Execute.

USE msdb;  
GO  
--  Adds a new job, executed by the SQL Server Agent service, called "HistoryCleanupTask_1".  
EXEC dbo.sp_add_job  
   @job_name = N'HistoryCleanupTask_1',   
   @enabled = 1,   
   @description = N'Clean up old task history' ;   
GO  
-- Adds a job step for reorganizing all of the indexes in the HumanResources.Employee table to the HistoryCleanupTask_1 job.   
EXEC dbo.sp_add_jobstep  
    @job_name = N'HistoryCleanupTask_1',   
    @step_name = N'Reorganize all indexes on HumanResources.Employee table',   
    @subsystem = N'TSQL',   
    @command = N'USE AdventureWorks2012  
GO  
ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX AK_Employee_NationalIDNumber ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX AK_Employee_rowguid ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
',   
    @retry_attempts = 5,   
    @retry_interval = 5 ;   
GO  
-- Creates a schedule named RunOnce that executes every day when the time on the server is 23:00.   
EXEC dbo.sp_add_schedule  
    @schedule_name = N'RunOnce',   
    @freq_type = 4,   
    @freq_interval = 1,   
    @active_start_time = 233000 ;   
GO  
-- Attaches the RunOnce schedule to the job HistoryCleanupTask_1.   
EXEC sp_attach_schedule  
   @job_name = N'HistoryCleanupTask_1'  
   @schedule_name = N'RunOnce' ;   
GO

For more information, see:

Share via