In this blog post, I gave out script to set up database mail for Sql Server 2005, if you already have smtp server up and running.

One thing you need to pay attention to is that Sql Server 2005 logs all those mail messages into msdb database, including mail attachments, if any. You probably can see where I am going now. If the logged mail messages and attachments are not archived or removed properly, you could get space issues in msdb, especially if you have large attachment files.

Here is a possible message you will get in Sql Server 2005 event log:

Could not allocate space for object ‘dbo.sysmail_attachments_transfer’.’PK__sysmail_attachme__7AF13DF7′ in database ‘msdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

This message is somewhat misleading. The reason is that if you go look at sysmail_attachments_transfer, this table alone may not be very big. However, sysmail_attachments, a different table in msdb that holds all mail attachments, can be pretty big.

This is a sample script you can use to schedule a job, that can remove all mail messages (including attachments) that are more than 1 month old. It uses a system stored procedure called sysmail_delete_mailitems_sp in msdb:

declare @CutoffDate datetime
set @CutoffDate = DATEADD(month, -1, GetDate())

exec msdb..sysmail_delete_mailitems_sp @sent_before = @CutoffDate

MSDN has a little more complex way of archiving those messages here.