Dealing with size of sysmail_mailitems table in MSDB database
By providing database mail functionality, Microsoft has given us enterprise level email functionality and I like this very much. Today one of my friend sent me chat request and asked me whether I can help him with email issues in his SQL Server as he knows that I am using this functionality since long and I always appreciate fast performance of the same.
My friend told me that his email functionality was working just fine but suddenly it stops working. Since, it was production environment, he didn’t had much time to investigate so my WILD thought for him to execute following SP in MSDB database.
He executed above given command immediately and after few minutes he ping me back and said thanks as email has started working.
“dbo.sysmail_start_sp” stored procedure is in the msdb database. This stored procedure starts the Database Mail queue that holds outgoing message requests and enables the Service Broker activation for the external program. It looks like, somehow, his email got stopped which is now started by executing the SP.
Since my friend was now tension free as his database mail has started, I asked him, just because of curiosity, what is the size of his “sysmail_mailitems” table in MSDB database and how many rows are there. I have also provided script to get these details which I have already provided in my blog earlier. Here are the links, if you are interested:
- Calculate Table & Schema size in SQL Server (Click Here)
- List of Table in Filegroup with table size in SQL Server (Click Here)
- Get row count for tables quickly in SQL Server (Click Here)
He come back with an answer, size of “sysmail_mailitems” table was 58+ GB and total number of rows found are approx 1 million. I can’t imagine MSDB database with 50+GB size. We must have to reduce the size. He had status of email stored in “sysmail_mailitems” table for more then year. Most of them are already delivered so there is no need to keep that record. I suggested him to clean “sysmail_mailitems” table if older messages are not in use in his business logic or not useful anywhere.
DECLARE @CutOffDate DATETIME
–suppose I want to delete records which are older then 3 month
SET @CutOffDate=Dateadd(d, -90, Getdate())
@sent_before = @CutOffDate;
@logged_before = @CutOffDate;
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.