Message task buries server

Question

Message task buries server

timscronin

SSCertifiable

Points: 6851
More actions
November 10, 2009 at 12:05 pm

#217454

For the past 2 months on my sql server 2005 Active/passive 64 bit cluster I have had the server buried at least once every week but system processes related to this command
WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body
FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout
This table usually had only a few records, but this command still buries the server. Anyone seen anything like this before?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well, it's obvious that this statement is reading messages from a Service Broker queue. What process is running this statement? Is it in a stored procedure somewhere? What envokes the SP?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

timscronin SSCertifiable Points: 6851 More actions · Answer 2

We have nothing running, but it seems there are related messages to dbmail in this, it seems all internal to sql server

John Rowan SSC Guru Points: 56440 More actions · Answer 3

What do you get when you run this:

SELECT * FROM sys.service_queues

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

timscronin SSCertifiable Points: 6851 More actions · Answer 4

5 records with these names

InternalMailQueue

ExternalMailQueue

QueryNotificationErrorsQueue

EventNotificationErrorsQueue

ServiceBrokerQueue

John Rowan SSC Guru Points: 56440 More actions · Answer 5

This has something to do with database mail. Can you check your database mail log and see if there are errors, especially near the times when you have the server hang?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

timscronin SSCertifiable Points: 6851 More actions · Answer 6

Never paid much attention to it, but the log shows is shutting down and starting up several times a day, is that related to when it sends out messages?

John Rowan SSC Guru Points: 56440 More actions · Answer 7

I believe so. Were there any errors or abnormal messages around the last time your server hung?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

timscronin SSCertifiable Points: 6851 More actions · Answer 8

No, two times when this happened this process got stuck and I could not kill it, had to reboot

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Can you please describe EXACTLY what behavior you are observing (CPU, IO, paging, etc)? How exactly did you identify this command/spid as the one causing the problem? if you run sp_who2 active does CPU and IO counters increase greatly between each run of the sp_who2 command? have you done file IO stalls and wait stats analysis? What version of sql server?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 10

Hi

Did you able to solve this problem? I am facing same problem with this query

"WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body

FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout"

And its consuming higher CPU.

Please let me know what steps you have taken to solve this

Thanks

noravia64 Newbie Points: 3 More actions · Answer 11

Well, I have the same issue.

What I have found is locks ending in deadlocks associated to the queries related to the message being sent.

I know the queries associated requires optimization, but the meesage sent is the result of those.

What should I be looking at then?