Skip to main content

Purge old data from dbo.sysmail_mailitems system table in msdb database - SQL Server

As a DBA, disk space is an important factor for daily monitoring and I encountered one issue of same for disk space. I found it based on disk space statistics report from all servers. I investigated disk space usage of all databases and found msdb system database went beyond 38 GB around. You can find the script at database size information. After database, it is needed to know where actually it was eating, which tables? I found one system table ‘dbo. sysmail_mailitems’ consumed high disk space and created space issue. Please read the post to get table size statistics for a particular database.

To make a disk space I removed some old data from dbo. sysmail_mailitems system table using a script. We can also delete data directly from this system table, but I found system stored procedure to purge old data from this table from here. Let me share with you,
  1. sysmail_delete_mailitems_sp :  Permanently deletes e-mail messages from the Database Mail internal tables.
  2. sysmail_delete_log_sp : Deletes events from the Database Mail log. Deletes all events in the log or those events meeting a date or type criteria.
And the script using these system procedures is following,
USE msdb; 


-- Purge data older than 30 days
SELECT @DeleteBeforeDate = Dateadd(d, -30, Getdate()) 

EXEC sysmail_delete_mailitems_sp 
  @sent_before = @DeleteBeforeDate 

EXEC sysmail_delete_log_sp 
  @logged_before = @DeleteBeforeDate 
To purge data manually, we can make it atomize with scheduled job. I do not know about setting which removed data automatically from this system table. Do you know about the same? This issue I experienced first time and I want to know about your experience if you can share here. What will be your comment to this post?