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.
May 13, 2020 at 6:56 pm
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.
May 13, 2020 at 7:17 pm
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.
May 13, 2020 at 7:41 pm
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?
May 13, 2020 at 7:57 pm
Just logging progess of the delete in PS.
May 13, 2020 at 8:44 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 13, 2020 at 9:22 pm
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
May 13, 2020 at 11:32 pm
I'm going to run the delete using the ID.
Thanks for the replies.
May 13, 2020 at 11:46 pm
If I wanted to use PS and Invoke-sqlcmd how would that fit with the SQL article?
THanks again.
May 14, 2020 at 3:45 pm
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.
May 14, 2020 at 5:10 pm
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
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
May 14, 2020 at 9:41 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 15, 2020 at 12:25 am
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
May 16, 2020 at 2:33 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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