Cleanup Sysmail -items from msdb

  • Bruin

    Ten Centuries

    Points: 1004

    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: 718992

    This might help: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-mailitems-sp-transact-sql?view=sql-server-ver15

    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: 1004

    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: 718992

    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: 1004

    Just logging progess of the delete in PS.

  • Jeffrey Williams

    SSC Guru

    Points: 88443

    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.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718992

    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: 1004

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

    Thanks for the replies.

  • Bruin

    Ten Centuries

    Points: 1004

    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: 718992

    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: 1004

    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 progress



    create 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 progress
    set totalRecords = @totalRecords;


    --begin to delete
    declare @rc int;
    set @rc = 1;

    while @rc > 0
    begin
    delete top (10000) from msdb.dbo.sysmail_mailitems where (mailitem_id >= 1 and mailitem_id <= 12000)
    set @rc = @@ROWCOUNT;

    update progress
    set recordsDeleted += @rc
    ,datetimestamp = getdate();
    end
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718992

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

    --  Delete in batches with progress put into table

    -- Table for holding progress
    CREATE TABLE progress
    (
    tableName VARCHAR(128)
    , totalRecords INT
    , recordsDeleted INT
    , starttime DATETIME2
    , endtime DATETIME2
    );
    GO

    -- dynamic batch control
    CREATE TABLE BatchDelete (TableName VARCHAR(128), BatchSize INT);
    GO

    INSERT INTO dbo.BatchDelete (TableName, BatchSize)
    VALUES
    ( 'sysmailitems' -- TableName - varchar(128)
    , 10000 -- BatchSize - int
    );
    GO


    DECLARE @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 delete
    DECLARE @rc INT;
    SET @rc = 1;

    WHILE @rc > 0
    BEGIN
    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: 88443

    I think there is a problem with this portion:

    DELETE TOP (@top)
    FROM msdb.dbo.sysmail_mailitems
    WHERE (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_mailitems
    WHERE mailitem_id <= @maxItemID;
    SET @rc = @@ROWCOUNT;

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Bruin

    Ten Centuries

    Points: 1004

    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: 88443

    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.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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