Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted December 1, 2016

Removing Email from msdb Database

By Greg Larsen

Is your msdb database growing bigger every day?  If so you might want to consider purging some of the history records from the msdb database.  One of those types of history records that are stored in the msdb database are records associated with using database mail.  If you are sending email using database mail and you are not periodically purging email information, then you might find out this is one of the reasons your msdb database is growing bigger.

In order to remove historic mail from the msdb database, Microsoft has provided two system stored procedures.  These stored procedures are named: sysmail_delete_mailitems_sp, and sysmail_delete_log_sp. 

The “sysmail_delete_mailitems_sp” permanently deletes email messages from msdb internal tables that contain database mail items.  This stored procedure allows you to delete email messages based on a date, or a specific status (sent, unsent, retry or failed).  If you want to delete email messages using a date this stored procedure will delete all email messages that are less than or equal to the date past to this stored procedure.  If no parameters are passed than all email messages will be deleted.  

The “sysmail_delete_log_sp” stored procedure deletes database mail log entries.  With this stored procedure you can delete log records based on a date and/or an event type.  When you pass a date value to this stored procedure it will delete all database mail log entries that are older or equal to the date specified.  If you want to delete mail log records based on one of the event types (success, warning, error or informational) then you use the event_type parameter.  If no parameters are passed, then all mail log entries are deleted.  

In reality you probably don’t need to keep email and log items forever.  You will need to decide in your environment how long you need to keep database mail information in the msdb database.  Once you know how long to keep mail information in msdb you can then build a process that purges your old database mail information using one or both of the system stored procedures listed above. 

Below you will find a script that delete all database email items and log entries from msdb database that are older than or equal 90 days: 

USE msdb;
GO
DECLARE @PurgeDate datetime;
--Calculate PurgeDate 90 days from today
SET @PurgeDate = dateadd(dd,-90,getdate());
-- Remove email and email attachments that are older or equal to @PurgeDate
EXEC sysmail_delete_mailitems_sp  @sent_before = @PurgeDate;
-- Removes rows from sys.sysmail_log that are older or equal to @PurgeDate
EXEC sysmail_delete_log_sp @logged_before = @PurgeDate;

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM