Zenn
👨‍🔧

SQL ServerメンテナンスプランをT-SQLで実行する

に公開
SQL
SQL Server
Transact-SQL
メンテナンスプラン
mssql
tech

はじめに

皆さんは、SQL Server を設計・構築する際にメンテナンスプランを活用していますか？
私は必ず活用しています。
メンテナンスプランは、SSMS（SQL Server Management Studio）から行うバックアップやインデックスの再構築などの保守作業を、スケジュールで自動実行できる便利な機能です。
しかし、システムの要件によっては、単にスケジュール通りに実行するのではなく、ある条件を満たした場合にのみ実行したいというケースも少なくありません。
たとえば、月次処理を手動で行うタイミングで、関連するメンテナンスプランも一緒に実行したい、といった場面です。
私は極度のめんどくさがり屋なので、「いっそ、T-SQLで記述しているバッチ処理の中にメンテナンスプランを組み込んでしまいたいな。。」と思い調べてみたところ、できてしまったので、日頃の感謝も込めて、ご紹介したいと思います。

メンテナンスプランとは

SQL Server の「メンテナンスプラン（Maintenance Plan）」とは、データベースの保守作業を自動化する機能です。
ックアップやインデックスの再構築、統計情報の更新、不要なデータのクリーンアップなど、定期的に実施すべきメンテナンスタスクをスケジュールして実行できるようにするためのものです。

以下のようなタスクをGUIで簡単に設定できます：

メンテナンスタスク 内容
データベースのバックアップ フル、差分、トランザクションログのバックアップを自動化
インデックスの再構築／再編成 パフォーマンス維持のために断片化を解消
統計情報の更新 クエリ最適化のために統計情報を最新化
データベース整合性チェック DBCC CHECKDB で整合性をチェック
履歴のクリーンアップ 古いバックアップ履歴やエラーログなどの削除
SQL Server Agent のジョブとして実行 上記タスクをスケジュール実行（例：毎日深夜3時）

(AI 検索結果より引用)

環境

  • SQL Server 2022 (オンプレミス)
    ※メンテナンスプランが使用できるバージョンなら本記事の内容は通用するはず。
    Azure SQL Database はメンテナンスプランがサポートされてないため適用不可。
    Azure SQL Managed Instance は適用可能。
    とのことですが、検証はしていません。

SSMSのGUIからメンテナンスプランを作成する

SSMSでT-SQLで実行したいメンテナンスプランを作成しておきます。

プラン名は、MaintenancePlan_1
サブラン名は、Subplan_1
というメンテンナスプランを作成するとします。

メンテナンスプランのジョブに関するテーブルについて

メンテナンスプランを作成すると、
msdb.dbo.sysjobs
テーブルにレコードができます。

msdb.dbo.sysjobsテーブルは、SQL Server Agent によって管理されているジョブ（自動化されたタスク）の情報を格納している システムテーブルです。

T-SQLで実行するためには、このテーブルからメンテナンスプランのjob_idを特定する必要があります。

メンテナンスプランをT-SQLで実行する

job_idをもとに、
msdb.dbo.sp_start_job
を使用してメンテンナスプランを実行します。

msdb.dbo.sp_start_job は、SQL Server Agentのジョブをスタートさせるシステムストアドプロシージャです。
(SQL Server Agent は、SQL Server に標準で付属しているジョブスケジューラです。)

以下は実際にメンテナンスプランを実行するT-SQLです。

-- メンテナンスプラン名
DECLARE @plan_name NVARCHAR(128);
SET @plan_name = N'MaintenancePlan_1.Subplan_1';
-- メンテナンスプラン名からjob_idを特定する
DECLARE @my_job_id uniqueidentifier;
SELECT 
    @my_job_id = job_id 
FROM 
    msdb.dbo.sysjobs 
WHERE 
    name = @plan_name
;
--jobを実行する
EXEC msdb.dbo.sp_start_job 
    @job_id = @my_job_id
;
--start_jobは非同期実行なのでループで完了を待機する
WHILE 1 = 1
BEGIN
    WAITFOR DELAY '00:00:05';--待機時間
    DECLARE @running AS INT;
    -- 指定のjob_idの実行状態を取得する
    SELECT 
        @running = COUNT(*) 
    FROM 
        msdb.dbo.sysjobactivity 
    WHERE 
        job_id = @my_job_id 
    AND 
        run_requested_date IS NOT NULL
    AND 
        start_execution_date IS NOT NULL
    AND 
        stop_execution_date IS NULL
    ;
    -- 停止しているならWHILEからBREAKする
    IF @running = 0 BEGIN 
        BREAK;
    END;
END;

実行結果 :

msdb.dbo.sysjobactivity テーブルに1件のレコードが追加され、stop_execution_date 列の値が更新されたことで、メンテナンスプランの実行が完了したことが確認できました。

まとめ

T-SQLからSQL Serverメンテンナスプランを実行を行いました。
T-SQLからメンテナンスプランを実行することで、大量データを扱う排他バッチ処理完了の直後に、統計情報の更新を行う、等のニーズにも対応しやすいかと考えます。

参考

  • Microsoft Learn SQL Server Agent

https://learn.microsoft.com/ja-jp/ssms/agent/sql-server-agent

  • Microsoft Learn sp_start_job (Transact-SQL)

https://learn.microsoft.com/ja-jp/sql/relational-databases/system-stored-procedures/sp-start-job-transact-sql?view=sql-server-ver16

  • SQL Server エージェントのジョブをクエリから同期的に実行する

https://blog.engineer-memo.com/2017/01/29/sql-server-エージェントのジョブをクエリから同期的に実/

Discussion