SQL Server Long Running Transaction – WAITFOR(RECEIVE conversation….DatabaseMail)

All we need is an easy explanation of the problem, so here it is.

I have recently implemented an Agent Job which checks SQL Server every 10mins for any long running queries and if detected it will send out a mail to recipients with the information. However since putting this in, I notice alot of the below query and wonder if this is something I should be concerned about:

WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

Understand its from Database Mail and the wait info is (1x: 62093ms)BROKER_RECEIVE_WAITFOR but should I need to worry or simply exclude it from the alerting.

Looking at it via sp_whoisactive can see that the open_transaction count is 1 and the status is suspended.

Any help is appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

From my experience, you should ignore it. This query is the one that comes from the program that uses SQL Broker. It’s normal. The program is simply waiting on SQL Broker to notify it this way. (Of course waiting on SQL Broker to notify will generate a long running wait.)

We are using the Broker and I have the same thing.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply