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

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 remove messages and events that are no longer needed. The following procedures create a SQL Server Agent job to automate the process.

1. The first procedure creates a job named Archive Database Mail with four steps.

2. The first step copies all messages from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_<year_month>.

3. The second step copies 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>.

4. The third step copies 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>.

5. The fourth step deletes the records of the transferred mail items from the Database Mail tables.

6. The fifth step deletes the events related to the transferred mail items from the Database Mail event log.

7. The final procedure schedules the job to run at the start of each month.

For this example, the archive tables are created in the msdb database. To reduce the size of the msdb database, the new tables might be located in a special archive database, or the rows could be exported to a text file, or just deleted. This example only moves the rows to new tables in the msdb database. For production use, you might want to add additional error checking and send an e-mail message to operators if the job fails.

To create a SQL Server Agent job

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

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

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

4. In the Category box, click the Database Maintenance.

5. In the Description box, type Archive Database Mail messages, and then click Steps.

To create a step to archive the Database Mail messages

1. On the Steps page, click 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 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. Click OK to save the step.

To create a step to archive the Database Mail attachments

1. On the Steps page, click 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. Click OK to save the step.

To create a step to archive the Database Mail log

1. On the Steps page, click 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. Click OK to save the step.

To create a step to remove the archived rows from Database Mail

1. On the Steps page, click 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. Click OK to save the step.

To create a step to remove the archived items from Database Mail event log

1. On the Steps page, click 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 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 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 ;
   

6. Click OK to save the step.

To schedule the job to run at the start of each month

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

2. On the Schedules page, click 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 on the first day of every month.

6. In the Daily frequency area, select Occurs once at 3:00:00 AM.

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

8. Click OK to save the job.

Security

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

See Also

Reference

Database Mail Views (Transact-SQL)

Database Mail and SQL Mail Stored Procedures (Transact-SQL)

Creating SQL Server Agent Jobs

Concepts

Database Mail