none
One of the service broker spid <spids> causing our tempdb full. How to troubleshoot what exactly the issue with service broker?

    Question

  • One of the service broker spid causing our tempdb full. How to troubleshoot what exactly issue with service broker

    Below is the broker_activated_tasks getting performed and No record found in transmission_queue tables

    spid

    database_id

    queue_id

    procedure_name

    execute_as

    47

    8

    87931635

    [dbo].<spname>

    1

    database_id

    queue_id

    state

    last_empty_rowset_time

    last_activated_time

    tasks_waiting

    8

    87931635

    RECEIVES_OCCURRING

    9/20/13 3:22 PM

    9/25/13 5:28 AM

    0


    Rahul

    Wednesday, September 25, 2013 3:41 PM

All replies

  • Hi Rahul,
     
    Thank you for your question. 
     
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
     
    Thank you for your understanding and support.

    Allen Li
    TechNet Community Support

    Friday, October 04, 2013 7:20 AM
    Moderator
  • Hi Rahul,

    From the output which is shared, it seems that the activity is performed by a system SPID (47). Please try the below steps to under the issue better. The status "Receives_occurring" seems to be fine : 

    Troubleshooting Activation Stored Procedures
    http://technet.microsoft.com/en-us/library/ms166102(v=sql.105).aspx

    **Check the waittype for the spid and understand if there is any resource bottleneck

    select * from master.sys.sysprocesses where spid =47
    go


    **Validate the script of Activation Stored procedure(ASP) which is associated with queue_id 87931635. 

    **Is ASP an "Internal Activation"  or "Event-Based"?

    Thursday, October 31, 2013 6:47 AM