# Cleanup Sysmail -items from msdb

• Bruin

Ten Centuries

Points: 1010

I took over a SQL instance that never did a cleanup of MSDB. The SysmailItems has close to 25gig of space used storing old email attachments. I know there is a system SP that does a cleanup using begin date \end date. I was looking for a wrapper using PS that would

delete 10,000 records at a time saving the MSDB LOG space, and more controllable.

Any working examples would be much appreciated.

Thanks.

• Steve Jones - SSC Editor

SSC Guru

Points: 720371

sysmail_delete_mailitems_sp - Database Mail messages and their attachments are stored in the msdb database. Messages should be periodically deleted to prevent msdb from growing larger than expected and to comply with your organizations document retention program. Use the sysmail_delete_mailitems_sp stored procedure to permanently delete e-mail messages from the Database Mail tables. An optional argument allows you to delete only older e-mails by providing a date and time.

• Bruin

Ten Centuries

Points: 1010

Thanks. I looked at that sp and it uses sent_date(Not an index). I was hoping to use a PS script and the mail_id from the table

to delete the records in blocks of say 10,000 records to prevent LOG issues and other current processes within MSDB. The PS script would log to a file every 2,000 deletes so I could check progress of the run.

Thanks.

• Steve Jones - SSC Editor

SSC Guru

Points: 720371

ah, sorry, I didn't read closely enough.

Are you using PoSh to control this somehow from outside SQL Server?

I'm guessing you want mail_id and then pick a range of ids to delete? The proc itself seems to just delete from the table with

  DELETE FROM msdb.dbo.sysmail_allitems

You could easily wrap this in a call with a WHERE clause that meets your need. Invoke-SqlCmd would work, though why not just use T-SQL and run this in batches of xx rows, with a DELETE TOP (2000) ... WHERE and then log something.

Are you trying to log what is deleted or just progress?

• Bruin

Ten Centuries

Points: 1010

Just logging progess of the delete in PS.

• Jeffrey Williams

SSC Guru

Points: 88587

You could add an index on the column send_request_date - nothing preventing that index from being created (except space and time - of course).

The other option would be to batch the deletes into daily/weekly/monthly groups.  Identify the oldest data available and use that to determine the first date range - then increment for each loop and call the stored procedure.

Note: the only other item that stored procedure does is to update the table sysmail_log with the user and row count.  However, this will only show up in the log if you set the logging level a higher level.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• Steve Jones - SSC Editor

SSC Guru

Points: 720371

An index is a good idea if you're not going off id. If you are, I'd just run this in T-SQL.

https://www.sqlservercentral.com/articles/deleting-large-number-of-records

• Bruin

Ten Centuries

Points: 1010

I'm going to run the delete using the ID.

Thanks for the replies.

• Bruin

Ten Centuries

Points: 1010

If I wanted to use PS and Invoke-sqlcmd how would that fit with the SQL article?

THanks again.

• Steve Jones - SSC Editor

SSC Guru

Points: 720371

You could build the SQL statement in PoSh by manipulating the strings that make it up. Or you could use parameters and a proc, changing the parameters that you send from PoSh and capturing output that would contain what you want to log.

• Bruin

Ten Centuries

Points: 1010

Do you see anything wrong with this code? I'm wanting to do Batches of 10,000 and based on the mailitem_ID counter found in the table. I going to feed it a month at  time.

--  Delete in batches with progress put into table -- Table for holding progresscreate table progress(tableName varchar(128),totalRecords int,recordsDeleted int    ,datetimestamp datetime); insert into progress (tableName, recordsDeleted) values('sysmailitems',0); declare @totalRecords int;select @totalRecords = count(*) from msdb.dbo.sysmail_mailitems(nolock); update progressset totalRecords = @totalRecords;  --begin to deletedeclare @rc int;set @rc = 1; while @rc > 0begindelete top (10000) from msdb.dbo.sysmail_mailitems where (mailitem_id >= 1 and mailitem_id <= 12000)set @rc = @@ROWCOUNT; update progressset recordsDeleted += @rc       ,datetimestamp = getdate();end
• Steve Jones - SSC Editor

SSC Guru

Points: 720371

This should work, but a few things I'd do to improve this:

--  Delete in batches with progress put into table-- Table for holding progressCREATE TABLE progress(    tableName VARCHAR(128)  , totalRecords INT  , recordsDeleted INT  , starttime DATETIME2  , endtime DATETIME2);GO-- dynamic batch controlCREATE TABLE BatchDelete (TableName VARCHAR(128), BatchSize INT);GOINSERT INTO dbo.BatchDelete (TableName, BatchSize)VALUES(   'sysmailitems' -- TableName - varchar(128)  , 10000          -- BatchSize - int    );GODECLARE @totalRecords INT      , @start        datetim2 = SYSUTCDATETIME()      , @top          INT;SELECT @totalRecords = COUNT(*)FROM   msdb.dbo.sysmail_mailitems (NOLOCK);INSERT INTO dbo.progress (tableName, totalRecords, recordsDeleted, starttime, endtime)VALUES(   'sysmailitems' -- tableName - varchar(128)  , @totalRecords  -- totalRecords - int  , 0              -- recordsDeleted - int  , @start, NULL);--begin to deleteDECLARE @rc INT;SET @rc = 1;WHILE @rc > 0BEGIN    SELECT @top = BatchSize    FROM   dbo.BatchDelete    WHERE  TableName = 'sysmailitems';    DELETE TOP (@top)    FROM msdb.dbo.sysmail_mailitems    WHERE (mailitem_id >= 1 AND mailitem_id <= 12000);    SET @rc = @@ROWCOUNT;    UPDATE progress    SET    recordsDeleted += @rc         , endtime = SYSUTCDATETIME()    WHERE  starttime = @start;END;

Why did I make changes?

First, I want to track what's going on, so I added times to this and I update the end time at each loop, so I know what's happening.

Why a second table? I've found batches sometimes cause blocking or issues. Rather than stopping or restarting this constantly and playing with the batch size, I can do this live. If I update the table, I can shrink or increase the batch size as I watch this happening. I haven't tried to track the time for each batch delete, but you could, but just inserting each loop. For me, I'm just looking at what's going, but if I stop this and restart tomorrow, this will add a new "sessions", which is useful to see how many records I deleted on different days.

HTH

• Jeffrey Williams

SSC Guru

Points: 88587

I think there is a problem with this portion:

DELETE TOP (@top)FROM msdb.dbo.sysmail_mailitemsWHERE (mailitem_id >= 1 AND mailitem_id <= 12000);SET @rc = @@ROWCOUNT;

Once this code deletes the mailitem_id's that are less than or equal to 12000 - it will not delete any additional items.

I think what you want to do is define the max item value that you want to delete and use that instead.

Declare @maxItemID int      , @purgeDate datetime = dateadd(day, datediff(day, 0, getdate()) - 90, 0); Select @maxItemID = max(sm.mailitem_id)   From msdb.dbo.sysmail_mailitems              sm  Where sa.send_request_date < @purgeDate;

And then use this for your delete:

DELETE TOP (@top)FROM msdb.dbo.sysmail_mailitemsWHERE mailitem_id <= @maxItemID;SET @rc = @@ROWCOUNT;

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• Bruin

Ten Centuries

Points: 1010

I need to do the cleanup in batches that's why I'm supplying the min\max  in the where clause. I already have the blocks of mailitem_id's in which I will be doing the controlled delete.

Thanks

for examples and comments

• Jeffrey Williams

SSC Guru

Points: 88587

Sorry - I don't understand.

Are you saying you are going to supply the min/max values each time?  If so - then why batch the process and create a loop?  Just provide those values each time you run the code.

The code I provided will batch delete 10,000 rows each iteration until no more records available to be deleted.  It will be limited by the highest mail ID that you want to keep - so all rows with a higher mail ID would not be deleted.

One thing you will definitely want to include in this process that is missing - you want to add a checkpoint after the delete and possibly a wait to allow for other processes to access the tables.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply