Delete Unsent Email from Sql server 2008 database mail

Below are the steps to clear Unsent email from Database mail table.

I had situation where sql server dbmail generated millions of email for one of my Insufficient Resource Error and keep sending me thousands of email in every second.

To resolve this issue I have stopped that "Insufficient Resource" Alert from sql server agent.

But it was keep sending me email because All those email are generated before I have stopped the alert.

Now the question was How to clean that earlier generated mail from DatabaseMail table.

I found the following solution for this issue. I could see from the "unsent mail" table that it would stop sending me email after certain time. But rather waiting for email to be cleared why would I not delete all the email.

Therefore I have followed below step to delete all unsent email from the table.

And I succeed.

SELECT * FROM msdb.dbo.sysmail_event_log;

-- to see number email remaining to be sent

select count(*) from msdb.dbo.sysmail_unsentitems;

-- delete all that remaining emails

delete from msdb.dbo.sysmail_unsentitems;

-- table is cleared and I have not received any single mail after that.

select * from msdb.dbo.sysmail_unsentitems;

--- additional info

o solve the issue, please follow these steps:

1.Enabled the mail service queue:
USE msdb
GO
ALTER QUEUE ExternalMailQueue WITH STATUS = ON
2.Delete the undeliverable emails.
DELETE FROM sysmail_unsentitems

Job Done!