建立維護計畫Create a Maintenance Plan

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

本主題描述如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio ,在 Transact-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

必須啟用 Agent XPs 伺服器組態選項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 EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase 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: