Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs

Applies to: SQL Server Azure SQL Managed Instance

Copies of Database Mail messages and their attachments are retained in msdb tables along with the Database Mail event log. Periodically you might want to reduce the size of the tables and archive messages and events that are no longer needed.

The following procedures create a SQL Server Agent job to automate the process.

To run T-SQL commands on your SQL Server instance, use SQL Server Management Studio (SSMS), the MSSQL extension for Visual Studio Code, sqlcmd, or your favorite T-SQL querying tool.

Consider error checking and monitor this job to send an e-mail message to operators if this archive job fails.

Optionally, you could move archived Database Mail data into a custom archive database outside of msdb, or export them from SQL Server.

You must be a member of the sysadmin fixed server role to execute the stored procedures described in this topic.

The first procedure creates a job named Archive Database Mail with the following steps.

1. Copy all messages from the Database Mail tables to a new table named after the previous month, in the format DBMailArchive__<year_month>.

2. Copy the attachments related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Attachments_<year_month>.

3. Copy the events from the Database Mail event log that are related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Log_<year_month>.

4. Delete the records of the transferred mail items from the Database Mail tables.

5. Delete the events related to the transferred mail items from the Database Mail event log.

6. Schedule the job to run periodically.

The following steps use SQL Server Management Studio (SSMS). Download the latest version of SSMS at aka.ms/ssms.

1. Connect to the SQL Server instance.

2. In Object Explorer, expand SQL Server Agent, right-click Jobs, and then select New Job.

3. In the New Job dialog box, in the Name box, type Archive Database Mail.

4. In the Owner box, confirm that the owner is a member of the sysadmin fixed server role.

5. In the Category box, select the Database Maintenance.

6. In the Description box, type Archive Database Mail messages, and then select Steps.

1. On the Steps page, select New.

2. In the Step name box, type Copy Database Mail Items.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following T-SQL statement to create a table named after the previous month, containing rows older than the start of the current month.

   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. Select OK to save the step.

1. On the Steps page, select New.

2. In the Step name box, type Copy Database Mail Attachments.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following statement to create an attachments table named after the previous month, containing the attachments that correspond to the messages transferred in the previous step:

   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. Select OK to save the step.

1. On the Steps page, select New.

2. In the Step name box, type Copy Database Mail Log.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following statement to create a log table named after the previous month, containing the log entries that correspond to the messages transferred in the earlier step:

   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. Select OK to save the step.

1. On the Steps page, select New.

2. In the Step name box, type Remove rows from Database Mail.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Database box, select msdb.

5. In the Command box, type the following statement to remove rows older than the current month from the Database Mail tables:

   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. Select OK to save the step.

1. On the Steps page, select New.

2. In the Step Name box, type Remove rows from Database Mail event log.

3. In the Type box, select Transact-SQL script (T-SQL).

4. In the Command box, type the following statement to remove rows older than the current month from the Database Mail event log:

   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. Select OK to save the step.

1. In the New Job dialog box, select Schedules.

2. On the Schedules page, select New.

3. In the Name box, type Archive Database Mail.

4. In the Schedule type box, select Recurring.

5. In the Frequency area, select the options to run the job periodically, for example once every month.

6. In the Daily frequency area, select Occurs once at <time>.

7. Verify that the other options are configured as you wish, and then select OK to save the schedule.

8. Select OK to save the job.

• General database mail troubleshooting steps