I have blogged several times about unusually large MSDB databases like here.
Most of the time the problem comes from the backup history which is never purged.
This time it’s different. Try to guess, if not the backup history, what can cause MSDB to increase abnormally in size?
It’s huge. The database size is almost 90GB. The 10% configuration on data files causes large 8GB autogrowth increments.
The problem comes from emails sent by the application directly through Database Mail. These emails have a very large HTML body.
Here are some information about the emails stored in this msdb.
select CAST(MIN(send_request_date) AS DATE) AS oldestEmail, Count(*) AS nbRows from msdb.dbo.sysmail_allitems
oldestEmail nbRows ----------- ----------- 2020-04-06 43935
There aren’t a lot of emails. There’s only about one year of emails stored.
select Count(*) AS nbRows, AVG(DATALENGTH(body))/1048576 AS avgSizeMB from msdb.dbo.sysmail_allitems where DATALENGTH(body) > 1048576 -- 1MB
nbRows avgSizeMB ----------- -------------------- 297 287
There are almost 300 emails with an HTML body exceeding 1 Megabytes size. The average size for these 300 emails is a 287MB of HTML body.
This HTML size is obviously abnormal for an email and probably for any HTML file.
select SUM(IIF(sent_status='failed', 0, 1)) AS emailsSent from msdb.dbo.sysmail_allitems where DATALENGTH(body) > 1048576 -- 1MB
emailsSent ----------- 0
None of those large emails has been sent successfully…
Looking at the Database Mail log we can see the following error message:
The mail could not be sent to the recipients because of the mail server failure. Exception Message: Cannot send mails to mail server. (Exceeded storage allocation. The server response was: 5.3.4 Message size exceeds fixed maximum message size).
Email size is often restricted to a few Megabytes to limit bandwidth usage.
These large emails have nowhere to go and are just bloating the msdb database.
I decided to delete old emails with a retention of 6 months.
To clean up this table we can use the following procedure: msdb.dbo.sysmail_delete_mailitems_sp
I used the following script to minimize the impact on the transaction log file.
use msdb go declare @retentionDate datetime = DATEADD(MONTH, -6, getdate()) declare @oldest_date datetime = (select min(send_request_date) from msdb.dbo.sysmail_allitems) while (@oldest_date < @retentionDate) begin print 'sysmail_delete_mailitems_sp ' + CAST(@oldest_date AS varchar) exec msdb.dbo.sysmail_delete_mailitems_sp @oldest_date -- Delete by 1 week increments set @oldest_date = DATEADD(WEEK, 1, @oldest_date) checkpoint WAITFOR DELAY '00:00:10' end
The application team has been informed so they can fix this email content issue.
I recommend checking the size of your system databases frequently to avoid any weird situation like this.