データベース メール メッセージやイベント ログをアーカイブする SQL Server エージェント ジョブを作成する方法

データベース メール メッセージと添付ファイルのコピーは、データベース メール イベント ログに記録されると同時に、msdb のテーブルに保持されます。このテーブルのサイズを縮小するためには、不要になったメッセージやイベントを定期的に削除する必要があります。次の手順では、この処理を自動化する SQL Server エージェント ジョブを作成します。

1. まず、5 つのステップから構成される "データベース メールのアーカイブ" という名前のジョブを作成します。

2. 最初のステップでは、すべてのメッセージをデータベース メールのテーブルから新しいテーブルにコピーします。新しいテーブルには、DBMailArchive_<year_month> の形式で、前の月を表す文字列を付加した名前を付けます。

3. 2 番目のステップでは、最初のステップでコピーしたメッセージの添付ファイルをデータベース メールのテーブルから新しいテーブルにコピーします。新しいテーブルには、DBMailArchive_Attachments_<year_month> の形式で、前の月を表す文字列を付加した名前を付けます。

4. 3 番目のステップでは、最初のステップでコピーしたメッセージに関連するデータベース メール イベント ログからのイベントを、データベース メールのテーブルから新しいテーブルにコピーします。新しいテーブルには、DBMailArchive_Log_<year_month> の形式で、前の月を表す文字列を付加した名前を付けます。

5. 4 番目のステップでは、移し変えたメール アイテムのレコードをデータベース メールのテーブルから削除します。

6. 5 番目のステップでは、移し変えたメール アイテムに関連するイベントをデータベース メールのイベント ログから削除します。

7. 最後に、毎月初めにこのジョブが実行されるようにスケジュールを設定します。

この例では、msdb データベースにアーカイブ テーブルが作成されます。msdb データベースのサイズを縮小するには、新しいテーブルを特別なアーカイブ データベースに配置します。あるいは、該当する行をテキスト ファイルにエクスポートするか、単に削除します。この例での動作は、単に該当する行を msdb データベースの新しいテーブルに移動しているだけです。実際の運用では、詳細なエラー チェックを追加したり、ジョブが失敗した場合には電子メール メッセージをオペレータに送信したりする必要があるでしょう。

SQL Server エージェントのジョブを作成するには

1. オブジェクト エクスプローラで [SQL Server エージェント] を展開し、[ジョブ] を右クリックして、[新しいジョブ] をクリックします。

2. [新しいジョブ] ダイアログ ボックスで、[名前] ボックスに「データベース メールのアーカイブ」と入力します。

3. [所有者] ボックスで、所有者が sysadmin 固定サーバー ロールのメンバであることを確認します。

4. [カテゴリ] ボックスで [データベースのメンテナンス] をクリックします。

5. [説明] ボックスに「データベース メール メッセージのアーカイブ」と入力し、[ステップ] をクリックします。

データベース メールのメッセージをアーカイブするステップを作成するには

1. [ステップ] ページで [新規作成] をクリックします。

2. [ステップ名] ボックスに「データベース メール アイテムのコピー」と入力します。

3. [種類] ボックスで [Transact-SQL スクリプト (T-SQL)] をクリックします。

4. [データベース] ボックスで [msdb] をクリックします。

5. [コマンド] ボックスに次のステートメントを入力します。このステートメントでは、前の月を表す文字列を付加した名前のテーブルが作成されます。このテーブルには、今月の開始日以前の行が格納されます。

   DECLARE @LastMonth nvarchar(12);
   DECLARE @CopyDate nvarchar(20) ;
   DECLARE @CreateTable nvarchar(250) ;
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_' + @LastMonth + '] FROM sysmail_allitems WHERE send_request_date < ''' + @CopyDate +'''';
   EXEC sp_executesql @CreateTable ;
   
   DECLARE @LastMonth nvarchar(12);
   DECLARE @CopyDate nvarchar(20) ;
   DECLARE @CreateTable nvarchar(250) ;
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_' + @LastMonth + '] FROM sysmail_allitems WHERE send_request_date < ''' + @CopyDate +'''';
   EXEC sp_executesql @CreateTable ;
   

6. [OK] をクリックしてステップを保存します。

データベース メールの添付ファイルをアーカイブするステップを作成するには

1. [ステップ] ページで [新規作成] をクリックします。

2. [ステップ名] ボックスに「データベース メール添付ファイルのコピー」と入力します。

3. [種類] ボックスで [Transact-SQL スクリプト (T-SQL)] をクリックします。

4. [データベース] ボックスで [msdb] をクリックします。

5. [コマンド] ボックスに次のステートメントを入力します。このステートメントでは、前の月を表す文字列を付加した名前の添付ファイル テーブルが作成されます。このテーブルには、前のステップでコピーされたメッセージに対応する添付ファイルが格納されます。

   DECLARE @LastMonth nvarchar(12);
   DECLARE @CopyDate nvarchar(20) ;
   DECLARE @CreateTable nvarchar(250) ;
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Attachments_' + @LastMonth + '] FROM sysmail_attachments 
    WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';
   EXEC sp_executesql @CreateTable ;
   
   DECLARE @LastMonth nvarchar(12);
   DECLARE @CopyDate nvarchar(20) ;
   DECLARE @CreateTable nvarchar(250) ;
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Attachments_' + @LastMonth + '] FROM sysmail_attachments 
    WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';
   EXEC sp_executesql @CreateTable ;
   

6. [OK] をクリックしてステップを保存します。

データベース メールのログをアーカイブするステップを作成するには

1. [ステップ] ページで [新規作成] をクリックします。

2. [ステップ名] ボックスに「データベース メール ログのコピー」と入力します。

3. [種類] ボックスで [Transact-SQL スクリプト (T-SQL)] をクリックします。

4. [データベース] ボックスで [msdb] をクリックします。

5. [コマンド] ボックスに次のステートメントを入力します。このステートメントでは、前の月を表す文字列を付加した名前のログ テーブルが作成されます。このテーブルには、前半のステップでコピーされたメッセージに対応するログ エントリが格納されます。

   DECLARE @LastMonth nvarchar(12);
   DECLARE @CopyDate nvarchar(20) ;
   DECLARE @CreateTable nvarchar(250) ;
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Log_' + @LastMonth + '] FROM sysmail_Event_Log 
    WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';
   EXEC sp_executesql @CreateTable ;
   
   DECLARE @LastMonth nvarchar(12);
   DECLARE @CopyDate nvarchar(20) ;
   DECLARE @CreateTable nvarchar(250) ;
   SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))
   SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Log_' + @LastMonth + '] FROM sysmail_Event_Log 
    WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';
   EXEC sp_executesql @CreateTable ;
   

6. [OK] をクリックしてステップを保存します。

データベース メールからアーカイブされた行を削除するステップを作成するには

1. [ステップ] ページで [新規作成] をクリックします。

2. [ステップ名] ボックスに「データベース メールからの行の削除」と入力します。

3. [種類] ボックスで [Transact-SQL スクリプト (T-SQL)] をクリックします。

4. [データベース] ボックスで [msdb] をクリックします。

5. [コマンド] ボックスに次のステートメントを入力します。このステートメントでは、データベース メールのテーブルから今月よりも古い行が削除されます。

   DECLARE @CopyDate nvarchar(20) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;
   EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @CopyDate ;
   
   DECLARE @CopyDate nvarchar(20) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;
   EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @CopyDate ;
   

6. [OK] をクリックしてステップを保存します。

データベース メール イベント ログからアーカイブされたアイテムを削除するステップを作成するには

1. [ステップ] ページで [新規作成] をクリックします。

2. [ステップ名] ボックスに「データベース メール イベント ログからの行の削除」と入力します。

3. [種類] ボックスで [Transact-SQL スクリプト (T-SQL)] をクリックします。

4. [データベース] ボックスで [msdb] をクリックします。

5. [コマンド] ボックスに次のステートメントを入力します。このステートメントでは、データベース メールのイベント ログから今月よりも古い行が削除されます。

   DECLARE @CopyDate nvarchar(20) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;
   EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @CopyDate ;
   
   DECLARE @CopyDate nvarchar(20) ;
   SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;
   EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @CopyDate ;
   

6. [OK] をクリックしてステップを保存します。

ジョブを毎月初めに実行するスケジュールを設定するには

1. [新しいジョブ] ダイアログ ボックスで [スケジュール] をクリックします。

2. [スケジュール] ページで [新規作成] をクリックします。

3. [名前] ボックスに「データベース メールのアーカイブ」と入力します。

4. [スケジュールの種類] ボックスで [定期的] をクリックします。

5. [頻度] 領域で、毎月最初の日にジョブを実行するオプションを選択します。

6. [一日のうちに繰り返される頻度] 領域で、[1 回 3:00:00 AM] を選択します。

7. その他のオプションが目的どおりに構成されていることを確認し、[OK] をクリックしてスケジュールを保存します。

8. [OK] をクリックしてジョブを保存します。

セキュリティ

このトピックで説明したストアド プロシージャを実行するには、sysadmin 固定サーバー ロールのメンバである必要があります。

関連項目

参照

データベース メール ビュー (Transact-SQL)

データベース メールと SQL Mail のストアド プロシージャ (Transact-SQL)

Creating SQL Server Agent Jobs

概念

データベース メール