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

適用対象:SQL ServerAzure SQL Managed Instance

データベース メール メッセージとその添付ファイルのコピーは、データベース メール イベント ログと共に msdb テーブルに保持されます。 このテーブルのサイズを縮小するためには、不要になったメッセージやイベントを定期的にアーカイブする必要があります。

次の手順では、この処理を自動化する SQL Server エージェント ジョブを作成します。

前提条件

SQL Server インスタンスで T-SQL コマンドを実行するには、 SQL Server Management Studio (SSMS)、 Visual Studio Code 用の MSSQL 拡張機能、 sqlcmd、またはお気に入りの T-SQL クエリ ツールを使用します。

推奨事項

このアーカイブ ジョブが失敗した場合は、エラー チェックを検討し、このジョブを監視して、オペレーターに電子メール メッセージを送信します。

必要に応じて、アーカイブされたデータベース メール データを msdb外のカスタム アーカイブ データベースに移動したり、SQL Server からエクスポートしたりできます。

アクセス許可

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

アーカイブ データベース メール ジョブを作成する

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

1. データベース メール テーブルのすべてのメッセージを、前の月にちなんだ新しいテーブルに、 DBMailArchive__<year_month>形式でコピーします。

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

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

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

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

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

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

以下の手順では、SQL Server Management Studio (SSMS) を使います。 最新バージョンの SSMS (aka.ms/ssms) をダウンロードします。

1. SQL Server インスタンスに接続します。

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

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

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

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

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

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

1. [ ステップ ] ページで、[ 新規] を選択します。

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

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

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

5. [コマンド] ボックスに、次の T-SQL ステートメントを入力して、前の月にちなんだテーブルを作成します。このテーブルには、現在の月の先頭より古い行が含まれています。

   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. [一日のうちの頻度] 領域で、[Occurs once at <time>] (<時刻> に 1 度実行) を選びます。

7. 他のオプションが必要に応じて構成されていることを確認し、[ OK] を 選択してスケジュールを保存します。

8. [OK] を選択してジョブを保存します。

関連コンテンツ

• 一般的なデータベース メールのトラブルシューティング手順