创建维护计划Create a Maintenance Plan

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中创建单服务器或多服务器维护计划。This topic describes how to create a single server or multiserver maintenance plan in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 通过使用 Management StudioManagement Studio,您可以通过以下两种方式之一创建这些维护计划:使用维护计划向导或设计图面。Using Management StudioManagement 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

若要创建多服务器维护计划,必须配置包含一个主服务器和一个(或多个)目标服务器的多服务器环境。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.

先决条件Prerequisite

必须启用 “代理 XP”服务器配置选项The Agent XPs Server Configuration Option must be enabled.

SecuritySecurity

权限Permissions

若要创建或管理维护计划,您必须是 sysadmin 固定服务器角色的成员。To create or manage Maintenance Plans, you must be a member of the sysadmin fixed server role.

使用 SQL Server Management StudioUsing SQL Server Management Studio

使用维护计划向导创建维护计划To create a maintenance plan using the Maintenance Plan Wizard

  1. 在对象资源管理器中,单击加号以便展开您要创建维护计划的服务器。In Object Explorer, click the plus sign to expand the server where you want to create a maintenance plan.

  2. 单击加号以便展开 “管理” 文件夹。Click the plus sign to expand the Management folder.

  3. 右键单击“维护计划” 文件夹,然后选择“维护计划向导” 。Right-click the Maintenance Plans folder and select Maintenance Plan Wizard.

  4. 按照向导中显示的步骤创建维护计划。Follow the steps of the wizard to create a maintenance plan. 有关详细信息,请参阅 Use the Maintenance Plan WizardFor more information, see Use the Maintenance Plan Wizard.

使用设计图面创建维护计划To create a maintenance plan using the design surface

  1. 在对象资源管理器中,单击加号以便展开您要创建维护计划的服务器。In Object Explorer, click the plus sign to expand the server where you want to create a maintenance plan.

  2. 单击加号以便展开 “管理” 文件夹。Click the plus sign to expand the Management folder.

  3. 右键单击“维护计划” 文件夹,然后选择“新建维护计划” 。Right-click the Maintenance Plans folder and select New Maintenance Plan.

  4. 按照创建维护计划(维护计划设计图面)中的步骤创建维护计划。Create a maintenance plan following the steps in Create a Maintenance Plan (Maintenance Plan Design Surface).

使用 Transact-SQLUsing Transact-SQL

创建维护计划To create a maintenance plan

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。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: