Recently, I received an urgent request from a client who was experiencing performance issues with their SQL Server database. The culprit turned out to be the MSDB database, specifically the sysmail_mailitems table. In this blog post, I will discuss the issue and provide a solution to manage the MSDB database effectively.

The Problem

The client’s system had been sending emails for several years, and each time an email was sent, the history of the email was saved in the MSDB database. Over time, this led to a significant increase in the size of the sysmail_mailitems table, causing performance degradation.

The Solution

To resolve the issue, I recommended deleting the unnecessary data from various history tables in the MSDB database. This would help free up space and improve performance. Here is the script 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

Running the above script helped free up a significant amount of space in the MSDB database. However, it’s important to note that shrinking a database is generally not recommended as it can lead to fragmentation and reduced performance. In this unique scenario, shrinking the database was a viable option to gain additional space. Here is the script I used to shrink the MSDB database:

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

After running the above script, the size of the MSDB database was significantly reduced from 18 GB to only 600 MB.

Conclusion

Managing the MSDB database is crucial for maintaining optimal performance in SQL Server. In this case, deleting unnecessary data from the history tables and shrinking the database helped resolve the performance issues faced by the client. However, it’s important to remember that shrinking a database should be done sparingly and only in unique scenarios like this one.

If you want to learn more about why shrinking a database is generally not recommended and how it can adversely affect performance, I encourage you to read my blog post on the topic: SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance.

Thank you for reading, and I hope this blog post has provided valuable insights into managing the MSDB database in SQL Server.