Truncate Database Mail table

Question

We have been having some drive space issues on our SQL server (2005) and I just ran some queries on the sys.allocation_units table and found that we have 26GB of database mail attachments. Apparently we have just been stashing this away in our msdb without any kind of clean-up for a couple years.

So I attempted using sysmail_delete_mailitems_sp but we're filling up our log and it's hitting our space limitation. I looked in this sys sproc and all it is really doing is running

DELETE FROM sysmail_allitems 

with some parameters and error handling. This is a system view that I'm assuming deletes from a collection of sysmail_xyz tables.

We do not need any of this old mail data. Everything we mail is already logged and archived in our application layer. Can I just run

TRUNCATE TABLE sysmail_allitems

It's a view but it's being DELETE'd so I'm wondering if I can TRUNCATE also. If not maybe I can

TRUNCATE TABLE sysmail_attachments 

but I'm afraid I'll orphan something that will break my system. Any suggestions?

Answer 1

Perhaps you could delete in chunks?

• Determine a date far back in the past for which you know there aren't any mails present in msdb
• Construct a WHILE loop to execute sysmail_delete_mailitems_sp for that date (@sent_before=@loop_date), then increment that date
• And so on until the present

That way you wouldn't fill up the log as much...