SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clearing out msdb.dbo.sysmail_mailitems...


Clearing out msdb.dbo.sysmail_mailitems...

Author
Message
jasona.work
jasona.work
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 11713
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... Whistling

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.
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27902 Visits: 39920
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jasona.work
jasona.work
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 11713
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
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 2775
Hello,


Is there a way to delete it automatically?

Regards
Durai Nagarajan
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5166 Visits: 4076
durai nagarajan (10/18/2012)
Is there a way to delete it automatically?
Scehdule a JOB :-D

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 2775
funny and thanks but is there no other option?

Regards
Durai Nagarajan
jasona.work
jasona.work
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 11713
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
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27902 Visits: 39920
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jasona.work
jasona.work
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 11713
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.
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27902 Visits: 39920
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search