I have a service broker (
retention = OFF ) which calls a stored procedure
--Register SP for Dequeue operation
ALTER QUEUE [TEST_Queue] WITH RETENTION = OFF,ACTIVATION
( STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = sp_dequeue,
EXECUTE AS owner
TRUSTWORTHY and broker is enabled
SP_dequeue basically does a
RECEIVE TOP(1) from the TEST_Queue
and processes the data. It was running fine for months, but suddenly the service broker has stopped calling the procedure
SP_dequeue. But when I select the queue I can see a lot of rows with
STATUS = 1 and
sys.transmission_queue is empty.
What all can be the reason ??? When I check the event log there is no error or message. In between machine was restarted. After restarting also the queue was working fine up to 2 weeks. Kindly help..