Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Clearing out msdb.dbo.sysmail_mailitems... Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 10:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 980, Visits: 5,114
OK, so we (I) finally noticed that the MSDB backup was rather large. In looking into the problem I found that the table dbo.sysmail_mailitems is taking about about 140,000,000KB (133GB if you prefer) It got this large for a couple reasons:
1. When we set up Database Mail, we didn't realize it would store the messages here...
2. Our previous backup method was more "automated" so we never noticed the backup getting as large as it did.
3. OK, yes, I also never thought to test restore MSDB...

So, I tried clearing it out using the stored procedure "sysmail_delete_mailitems" which did not help. Even setting it to delete a relatively small date range (@sent_before,) it runs so long we need to cancel it.

I'm working on a copy of the DB now, and I thought I'd try just truncating the table (we don't really need to store all these old messages,) but that failed due to some foreign key constraints. So I'm coming to you folks for help. What's going to be the best way to clear this thing out?

Can I do a "delete from..." to blow out the table? Or is this going to run into the problem of the FKs also? Will a straight "delete from..." take a long time as well (in which case I could put a "where" clause on the end, and just keep running it, blowing out a handful of records every time...)

The DB *IS* in Simple Recovery, although based on what I've read I would expect the LDF to grow quite a bit during a delete operation. That I could then control by backing up the DB and checkpointing, correct? Or would I need to also do a "DBCC shrinkfile" on it?

Thanks,
Jason A.
Post #1373949
Posted Wednesday, October 17, 2012 11:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 12,910, Visits: 32,025
jason if you sp_helptext sysmail_delete_mailitems_sp in the msdb database,
you'll see it's using a catch-all query to delete fromt eht able, and only one table;
after a bunch of error checking, this is the core of the proc:
   DELETE FROM msdb.dbo.sysmail_allitems 
WHERE
((@sent_before IS NULL) OR ( send_request_date < @sent_before))
AND ((@sent_status IS NULL) OR (sent_status = @sent_status))


i have this saved in my snippets for deleting old mail jobs, which deletes from two tables in msdb if you peek at the proc code.
USE MSDB;
GO

DECLARE @DeleteBeforeDate DateTime
SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate


so you could do a simple DELETE:
   DELETE FROM msdb.dbo.sysmail_allitems 
DELETE FROM msdb.dbo.sysmail_log

as a proof of concept, i just did exactly that on my dev machine; cleared out 38K rows of stuff. no foreign key errors, just clean deletes.

hope that helps!


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1373957
Posted Wednesday, October 17, 2012 11:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 980, Visits: 5,114
Lowell I'll give those a try once my re-restore of the DB on my test box completes...
It only takes about an hour-and-a-half or so to restore...


Thanks!
Jason
Post #1373962
Posted Thursday, October 18, 2012 12:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
Hello,


Is there a way to delete it automatically?


Regards
Durai Nagarajan
Post #1374154
Posted Thursday, October 18, 2012 5:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
durai nagarajan (10/18/2012)
Is there a way to delete it automatically?
Scehdule a JOB


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1374279
Posted Thursday, October 18, 2012 7:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
funny and thanks but is there no other option?

Regards
Durai Nagarajan
Post #1374365
Posted Thursday, October 18, 2012 7:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 980, Visits: 5,114
While it was a bit of snarky reply, it's also true. If you want to automate something like this, it's going to have to be scheduled somehow. Either using SQL Agent Jobs, Windows Task Scheduler and batch files, or a reminder on someone's calendar.

Jason
Post #1374369
Posted Thursday, October 18, 2012 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 12,910, Visits: 32,025
the scriptlet i posted is what i've put in a scheduled job before;
just letting it run once a day deletes anything older than 30 days;
that's what would be my simple sugestion to keep the data under control, especially if you have a fairly busy usage of database mail.
DECLARE @DeleteBeforeDate DateTime 
SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1374373
Posted Monday, October 22, 2012 9:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 980, Visits: 5,114
My thanks to Lowell!

Using your Delete commands, plus adding one to delete from dbo.sysmail_mailitems, the DB is down to about 23GB from 133GB!

Set up an Agent job to run once a week and keep the last 30 days of records in all three tables, so I shouldn't have to worry about that one again...

Once more, thank you!
Jason A.
Post #1375539
Posted Monday, October 22, 2012 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 12,910, Visits: 32,025
jasona.work (10/22/2012)
My thanks to Lowell!

Using your Delete commands, plus adding one to delete from dbo.sysmail_mailitems, the DB is down to about 23GB from 133GB!

Set up an Agent job to run once a week and keep the last 30 days of records in all three tables, so I shouldn't have to worry about that one again...

Once more, thank you!
Jason A.

Glad to hear you've got it handled, Jason!
Maybe post your final solution so others can benefit from your research?

i think the other peice of the puzzle is deleting old job histories, right? i think the view is sysjobhistory
how big is your table right now? is it most of that remaining gigs of space?
exec sp_spaceused 'msdb.dbo.sysjobhistory'


again, from my snippets for maint of msdb, i have this:
DECLARE @oldestdate DATETIME
SET @oldest = GETDATE() - 30
EXEC SP_PURGE_JOBHISTORY @oldest_date = @oldestdate




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1375557
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse