Post reply

service broker

  • December 3, 2011 at 1:14 am

    #248948

    Hi Experts,

    since two days am facing problem with service broker. its just like data is not dequeuing from queue.

    usually data should dequeued automatically,but since two days data is enqueing to queue in service broker and not dequeuing. for time being am manually executing the SP in queue.

    How could i fix this issue in automatically dequeing data from queue.

    TIA...

  • December 3, 2011 at 2:59 am

    #1416261

    There are two sources I can recommend for descriptions of SSSB troulbeshooting:

    Rusanus blog[/url] (not limited to the article this link points to) and

    Johans (aka ALZDBA) article[/url].

    Those two should help you to narrow down the issue.

    If you need further advice, please post what you've found so far we'll try to help you further.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 3, 2011 at 3:32 am

    #1416266

    Perhaps queue activation is disabled? Can you post the row from sys.service_queues corresponding to your misbehaving queue?

  • December 5, 2011 at 6:30 am

    #1416640

    hi,

    the results of the queue is am attaching the screenshot.

  • December 5, 2011 at 6:45 am

    #1416653

    MasterDB (12/3/2011)

    Hi Experts,

    since two days am facing problem with service broker. its just like data is not dequeuing from queue.

    usually data should dequeued automatically,but since two days data is enqueing to queue in service broker and not dequeuing. for time being am manually executing the SP in queue.

    How could i fix this issue in automatically dequeing data from queue.

    TIA...

    Please provide more information. The following is a minimum to troubleshoot this.

    1. Queue, Message, Contract, Service, and route creation (if using locally created, note that). Full SQL code for each.

    2. The status of is_broker_enabled, is_trustworthy_on, suser_name(owner_sid) from sys.databases for each database using service broker.

    3. counts of each of the queues and the sys.transmission_queue.

    4. for any message is sys.transmission_queue, the a sample of each of the columns, you can omit the message_body field, but we need the others as they will help.

  • December 5, 2011 at 7:14 am

    #1416672

    Service broker is enabled, Trustworthy = on, owners of the databases is correct only

    and there are no records found in sys.transmission_queue

    query of queue is :

    Create queue dbo.[xxx] with status = on, retention = off, activation ( status=on,procedure name = [XXX],max_queue_readers = 10, Execute as owner) on [Primary]

  • December 5, 2011 at 7:39 am

    #1416683

    Hello experts,

    i went through the reports tab of service broker under that report in queue pane the status of my queue is showing inactive..

    i don't know whether this might be a problem. if this has been the problem then how can i change the status of my queue.

  • December 5, 2011 at 7:45 am

    #1416689

    Inactive just means that it is currently not receiving anything.

  • December 5, 2011 at 7:48 am

    #1416691

    so, how to make that queue available...

  • December 5, 2011 at 7:58 am

    #1416704

    Good question. Can you verify that the activation procedure name match the name of the procedure.

  • December 5, 2011 at 8:01 am

    #1416709

    if i execute the procedure manually it is dequeuing, but it is not dequeuing automatically from that procedure..

  • December 5, 2011 at 9:50 am

    #1416815

    MasterDB (12/5/2011)

    if i execute the procedure manually it is dequeuing, but it is not dequeuing automatically from that procedure..

    Sounds like the activation procedure isn't set or is running under a different name, or is possibly having security problems. Check the Error Log for a few of those, then you'll have to dig through the roll-scripts in detail to find the cause.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

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

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