It seems that the two things you are trying to accomplish are (a) a single process for sending mail and (b) the ability to check the status of sent e-mail messages later.
If this is your requirement, it is totally acceptable to have a table to which various operations write simple information about e-mail notifications which are sent. However, you do not need to store the whole message or the message status.
From the Technet entry for sp_send_dbmail:
When mail is successfully placed in the Database Mail queue,
sp_send_dbmail returns the
mailitem_id of the message.
And, from an MSDN walkthrough titled How to: Check the Status of E-Mail Messages Sent With Database Mail (Transact-SQL):
Database Mail keeps copies of outgoing e-mail messages and displays them in the
sysmail_faileditems views of the
msdb database. The Database Mail external program logs activity and displays the log through the Windows Application Event Log and the
sysmail_event_log view in the msdb database. To check the status of an e-mail message, run a query against this view. E-mail messages have one of four possible statuses: sent, unsent, retrying, and failed.
All you need to store, then, is the ID of the message which was queued, along with any application-specific information (such as which operation generated the notification, or the reason the notification was generated). When you are ready to get the status of a message, you can join back to one of the views described in the walkthrough above using the mailitem_id you saved to your table. If you do not have such application-specific data, there is no need for a custom tracking table at all, since you can search the
sysmail* views by recipient.
As for the "single process for sending mail" approach, I have used that abstraction myself and I don't see anything wrong with it, though I would offer some suggestions and caveats.
- If the reason you are polling the table every minute is that the messages are time-sensitive, this is probably not a good approach and you really should consider moving the
sp_send_dbmail call back into to the operation that needs to send notifications.
- When you call
sp_send_dbmail inside a transaction, the message is never sent if the transaction is rolled back. I mention that because you said you want the e-mail to be sent only when the transactions succeed.
- If you do need to keep some kind of "message builder" table to which various operations submit pending notifications, there is no need to keep records in this table once they have been processed. From that point forward, all the data you need should be available in the
sysmail* views, combined (if necessary) with the tracking table above.
I would like to provide an example where I have used this kind of abstraction. I have an application in production for which notifications are generated several times a minute, but users only need to receive these notifications once per day (or, in some cases, a few times per day). Each operation which needs to send a notification submits the message to the notification queue, and once it is submitted, the operation does not have any need to know how the notification is sent out. The centralized messaging process determines whether the message needs to be sent out immediately or should be aggregated with similar messages. For messages that can be aggregated, the messaging processor—which runs as a SQL Agent job—takes all the notifications for a single recipient and combines them once per day into a single e-mail.