Post reply

Message task buries server

  • 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?

  • November 10, 2009 at 1:15 pm

    #1077470

    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

  • November 10, 2009 at 2:01 pm

    #1077508

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

  • November 10, 2009 at 2:05 pm

    #1077510

    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

  • November 10, 2009 at 3:06 pm

    #1077559

    5 records with these names

    InternalMailQueue

    ExternalMailQueue

    QueryNotificationErrorsQueue

    EventNotificationErrorsQueue

    ServiceBrokerQueue

  • November 11, 2009 at 6:49 am

    #1077755

    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

  • November 11, 2009 at 7:36 am

    #1077788

    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?

  • November 11, 2009 at 9:04 am

    #1077864

    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

  • November 11, 2009 at 9:37 am

    #1077888

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

  • November 12, 2009 at 7:55 am

    #1078361

    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

  • April 11, 2012 at 8:34 am

    #1471895

    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

  • August 17, 2012 at 4:24 pm

    #1527277

    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?

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

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