In yesterday’s post Setting up SQL Server Database Mail with gmail, I showed you how to setup SQL Server Database Mail to work with a gmail account, today we are going to look at doing some Database Mail maintenance.
Database Mail messages and their attachments are stored in the msdb database. If you don’t periodically delete these messages then the msdb database might growe larger than expected.
There are two stored procedure that you can call to delete items related to mail in the msdb database. The first stored procedure we are look at is sysmail_delete_mailitems_sp. The sysmail_delete_mailitems_sp stored procedure can be called with two optional arguments
[ @sent_before= ] ‘sent_before’
Deletes e-mails up to the date and time provided as the sent_before argument. sent_before is datetime with NULL as default. NULL indicates all dates.
[ @sent_status= ] ‘sent_status’
Deletes e-mails of the type specified by sent_status. sent_status is varchar(8) with no default. Valid entries are sent, unsent, retrying, and failed. NULL indicates all statuses.
If I want to delete everything older than 2 days, I can use the following proc call
DECLARE @Date datetime SELECT @Date = DateAdd(dd,-2,Getdate()) EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date
If I want to delete all the items that have failed I can call the proc like this
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_status = 'failed'
Of course we can also combine the arguments, this will wipe out all the failed messages older than 2 days
DECLARE @Date datetime SELECT @Date = DateAdd(dd,-2,Getdate()) SELECT @Date EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date , @sent_status = 'failed'
- * *The next proc we will look at is sysmail_delete_log_sp. You can use the sysmail_delete_log_sp stored procedure to permanently delete entries from the Database Mail log. This proc also has two optional arguments
[ @logged_before = ] ‘logged_before’
Deletes entries up to the date and time specified by the logged_before argument. logged_before is datetime with NULL as default. NULL indicates all dates.
[ @event_type = ] ‘event_type’
Deletes log entries of the type specified as the event_type. event_type is varchar(15) with no default. Valid entries are success, warning, error, and informational. NULL indicates all event types.
Here is how we will delete all events in the log older than two days
DECLARE @Date datetime SELECT @Date = DateAdd(dd,-2,Getdate()) EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date
Here we are deleting all the events with an event type of success
EXECUTE msdb.dbo.sysmail_delete_log_sp @event_type = 'success'
And just like before, we can combine the two proc calls and wipe out everything with an event type of success and older than two days
DECLARE @Date datetime SELECT @Date = DateAdd(dd,-2,Getdate()) EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date,@event_type = 'success'
I have a job running in SQL Agent, this job runs at 4 AM everyday and runs these two stored procedures to keep my msdb database from growing out of control. Just keep this in mind if you are staring out with Database Mail