How to Auto Maintain Database Emails in SQL Server

If the Database Mail in your SQL Server is used extensively, all messages and attachments from those emails would be saved in the MSDB database. Eventually, MSDB database will grow in size and occupy more disk space. This will also increase the time taken to complete backup of system databases. In this article, we will learn how to automate the maintenance of SQL Server database email

Using system stored procedures

Maintain Database Emails In SQL ServerFor this automation, we would be using sysmail_delete_mailitems_sp. It is SQL Server’s system stored procedure. This stored procedure has two input parameters. First is the ‘@sent_before’ and it accepts date value. Second is the ‘@sent_status’. Failed, unsent, sent, and retrying are valid entries for the second parameter.

Executing this script will create a new SQL Server agent job and it would be scheduled to run on Monday, Wednesday, Friday, and Sunday at 12:00 AM. This job deletes all items from the MSDB database.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Automatic_MailItem_Maintenance', 
   @enabled=1, 
   @notify_level_eventlog=0, 
   @notify_level_email=0, 
   @notify_level_netsend=0, 
   @notify_level_page=0, 
   @delete_level=0, 
   @description=N'This job automatically cleans mail items', 
   @category_name=N'[Uncategorized (Local)]', 
   @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1', 
   @step_id=1, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_success_step_id=0, 
   @on_fail_action=2, 
   @on_fail_step_id=0, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, @subsystem=N'TSQL', 
   @command=N'-- Declaring a variable to hold date value
DECLARE @todayis datetime 
-- Get the system date and store it in the variable @todayis
SET @todayis = GETDATE() 
--Pass the variable @todayis as parameter and delete mail items
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @todayis 
', 
   @database_name=N'msdb', 
   @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule1', 
   @enabled=1, 
   @freq_type=8, 
   @freq_interval=43, 
   @freq_subday_type=1, 
   @freq_subday_interval=0, 
   @freq_relative_interval=0, 
   @freq_recurrence_factor=1, 
   @active_start_date=20170925, 
   @active_end_date=99991231, 
   @active_start_time=0, 
   @active_end_time=235959, 
   @schedule_uid=N'83601a93-52cf-46b7-8a6a-93b56c0ad6d6'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
   IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Modify it

SQL Server Database EmailsAs per your requirement, you can easily edit this job’s schedule from SQL Server agent. If you are not interested in deleting all email items from the MSDB database, modify the job’s script to delete mail items based on the sent status. Deleting email items include an email’s message and attachments. However, this will not delete entries made for a mail item in the sysmail_event_log. By default, members of sysadmin will have permission to execute this system stored procedure. It is a good practice to backup system databases on daily basis. This will help you to restore system databases during SQL Server recovery.
Regularly backup your msdb database. Backup file will help you to recover a corrupted mdf file and retrieve mail items sent using database mail.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst recovery and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.