Delete Mail from DB Mail Queue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Delete Mail from DB Mail Queue

Author

Topic

jmcl
Starting Member

6 Posts

Posted - 2007-04-16 : 09:25:11

I had a very recent problem where an excess number of mail records were queued. I ran sysmail_delete_mailitems_sp and deleted them from the InternalMailQueue, but it looks like they still exist in the ExternalMailQueue as I keep getting messages in the log similar to "Mail Id 2277459 has been deleted from sysmail_mailitems table. This mail will not be sent". The MailItem_Id keeps incrementing so it looks like it's working its way through. The problem is we can't use it to sent current mail until it gets through about another 3 million records.
If there is some way to delete those records from the External Mail Queue or fast-forward the system to the current records that would be very helpful. Any ideas?

John

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-16 : 09:44:45

Do not cross post

CODO ERGO SUM

catalin.enescu
Starting Member

1 Post

Posted - 2007-04-25 : 11:36:11

Try this script can help you (remove top 1000), it's slow but works
You can also stop mail service

declare @conversation_handle uniqueidentifier
declare cr cursor for
select top 1000 conversation_handle
from ExternalMailQueue
where conversation_handle is not null
order by queuing_order desc
open cr
fetch next from cr into @conversation_handle
while @@fetch_status=0
begin
begin try
END CONVERSATION @conversation_handle;
-- print cast(@conversation_handle as varchar(max))+' OK'
end try
begin catch
print cast(@conversation_handle as varchar(max))+' Failed'
end catch
fetch next from cr into @conversation_handle
end
close cr
deallocate cr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources