SQL SERVER – Stop Growing MSDB Database by Removing sysmail_mailitems History

Received an email “Need your urgent help On Demand, our MSDB Database has grown too big and we need help to check our sysmail_mailitems table. We are also facing performance issues.” I have been an independent consultant for a while and one of the services I provide is “On Demand (50 minutes)” service. This service is very helpful for organizations who are in need immediate help with their performance tuning issue. Though, I have set working ours for my regular clients, every single day, I keep two hours available for this particular offering. This way, I can make sure that anyone who urgently needs my help, can avail the same. Click here to read more about it.

Well, I immediately allocated one of the two hours which I keep available to the individual who sent me an email.

The performance degradation of the system was due to reason one of the stored procedure was sending email upon triggering various activities. Every single time when the email was sent the history of the email is saved in the MSDB database. As the system was sending emails for many years, the system had slowed down quite a bit and the size of the MSDB database was grown very much. Upon investigating, I realized that the size of the table dbo.sysmail_mailitems was over 10 GB and the size of dbo.sysmail_attachments was about 2 GB. Both the table contained history of email sent out in the last 5 years.

Workaround / Resolution:

The resolution was to delete data from various history table of the MSDB. First, we took back of our database and after that I deleted all the history from various tables excluding last 30 days data. Here is the script which I used.

USE MSDB
GO
DECLARE @varDate DATETIME
-- Set date to 30 days ago
SET @varDate = DATEADD(d,-30,GETDATE());
-- delete from sysmail_attachments
DELETE FROM dbo.sysmail_attachments
WHERE Last_mod_date < @varDate;
-- delete from sysmail_send_retries
DELETE FROM dbo.sysmail_send_retries
WHERE Last_send_attempt_date < @varDate;
-- delete from sysmail_allitems
EXEC Sysmail_delete_mailitems_sp
@Sent_before = @varDate;
-- delete from sysmail_log
EXEC Sysmail_delete_log_sp
@Logged_before = @varDate;
GO

Well, once we ran above script the database had free up some good amount of space. Though we all know database shrinking is not a good option, this was a good case when we could shrink database and gain additional space. We ran following script on the database and it freed up quite a lot of empty space.

USE [msdb]
GO
DBCC SHRINKDATABASE(N'MSDB')
GO
USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY)
GO

After running above script, our msdb database which was of 18 GB came down to only 600 MB.

I strongly encourage everyone to read my blog post about SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance, which explains why shrinking database is not a good idea and how it adversely affect performance. As I said this was a unique scenario where we have to shrink the database.

Well, that’s it. After 50 minutes of consultancy, my client was very happy as we were able to bring back their performance to state which made them very happy.

Reference: Pinal Dave (https://blog.sqlauthority.com)