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

Question

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.

Answer 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.

Just wanted to add a detail: Any application waiting on SQL Broker will also use a thread from the threadpool of your server. So if you have multiple databases using the Broker on your server, keep in mind that threads are being reserved to wait on the Broker. I've had an issue on a server with hundreds of databases running out of threads because they all used the broker.