Cleanup pending mail queue

I see mail box is flooding with alerts, and many more in Queue to shoot our outlook mailbox.

First step is to stop the DBmail

Use MSDB

sysmail_stop_sp

This stored procedure Stops Database Mail by stopping the Service Broker objects that the external program uses.

Check the mail queue length with below query.

SELECT count (*)

FROM [msdb].[dbo].[ExternalMailQueue] WITH(NOLOCK)

Below query will clean up the external queue in service broker.

Use MSDB

declare @conversation uniqueidentifier

while exists (select 1 from dbo.ExternalMailQueue)

begin

set @conversation = (select top 1 conversation_handle from dbo.ExternalMailQueue)

end conversation @conversation with cleanup

end

Again check with mail queue length using query in step 2

Once the entire mail queue is cleared, please start the DBmail with below query.

Use MSDB

sysmail_start_sp