Share via

データベース メール メッセージやイベント ログをアーカイブする 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 ;

  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 ;

  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 ;

  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 ;

  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 ;

  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

ヘルプおよび情報

SQL Server 2005 の参考資料の入手