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

  • [アーティクル]

適用対象:SQL ServerAzure SQL Managed Instance

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

はじめに

前提条件

アーカイブ データを格納する新しいテーブルが、特別なアーカイブ データベース内にある場合があります。 代わりに、行をテキスト ファイルにエクスポートすることができます。

Recommendations

運用環境では、詳細なエラー チェックを追加したり、ジョブが失敗した場合には電子メール メッセージをオペレーターに送信したりする必要があるでしょう。

アクセス許可

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

プロセスの概要

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

    1. データベース メール テーブルのすべてのメッセージを、DBMailArchive_year_month>形式<で前月の後にという名前の新しいテーブルにコピーします。

    2. 最初の手順でコピーしたメッセージに関連する添付ファイルを、データベース メール テーブルから、前の月にちなんだ名前の新しいテーブルにDBMailArchive_Attachments_<year_month>形式でコピーします。

    3. 最初の手順でコピーしたメッセージに関連するデータベース メール イベント ログのイベントを、データベース メール テーブルから、前の月の後にという名前の新しいテーブルにDBMailArchive_Log_<year_month>形式でコピーします。

    4. 移し変えたメール アイテムのレコードをデータベース メールのテーブルから削除します。

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

  • ジョブが定期的に実行されるようにスケジュールを設定します。

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

    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 ;

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

概要

ジョブが定期的に実行されるようにスケジュールを設定するには

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

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

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

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

  5. [頻度] 領域で、たとえば毎月 1 回など、定期的にジョブを実行するオプションを選択します。

  6. [毎日の頻度] 領域で<、[一度>に 1 回発生] を選択します。

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

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

概要