1

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?

TT.
  • 15,774
  • 6
  • 47
  • 88
Larry Grady
  • 469
  • 7
  • 24
  • Offtopic, not a programming question. Try the DBA site. – Marc B Feb 10 '16 at 21:29
  • AFAIK you cannot truncate a view. – SQLChao Feb 10 '16 at 21:33
  • 1
    You can't truncate a view. How would it work if you truncated a view that referenced 6 tables? Which of those tables would it truncate? You will have to delete this data in batches so it doesn't overload your log files. – Sean Lange Feb 10 '16 at 21:39

1 Answers1

2

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...

TT.
  • 15,774
  • 6
  • 47
  • 88