Service borker Target queue disabled automatically

Question

Hi,

I'm getting problem with below given sceniro.

Scenario:

Get a request to process the SSIS package in service broker queue. (using internal activation, max queue reader is 1)

The activated procedure is calling to another procedure to execute the SSIS package that takes around 1 to 2 min

Problem:

If the queue is not activated and calling the stored procedure manually it is working fine (process the request, send response and close the conversation)

The problem starts when I activate the queue to process request automatically. The Target got disable in middle process (no response, not ETL execution)

How should I overcome with this problem?

Many thanks for your kind help; I have expended many hours to fix this problem.

 

Answer 1

More than likely there is an error in your activation stored procedure.  The queue will get disabled after X number of activation errors.  Your best bet is to have try...catch logic in your activation stored procedure that will log any errors to an exception table.  This way you can see what errors are being thrown by your activation stored procedure.

Answer 2

Hello Madhukar,

You need to check :

1. If the procedure gets invalidated by any user activity: - http://blogs.msdn.com/b/batala/archive/2010/11/23/sql-server-encounters-memory-pressure-and-generate-701-error-due-to-incorrect-configuration-of-service-broker.aspx

2. Check if you are handling the messgaes in a right way because if the messages are of wrong format or log any erros(poised messages) then in that case your activation stored will stop working. Check this article from the BOL : -http://msdn.microsoft.com/en-us/library/ms789028.aspx

 

 

 

---

Harsh Chawla(MSFT) Personal Blog:-http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/