Server Broker Error Case

Output Queue

A service uses Service Broker to send a message to another service. Service

Broker does this by putting the message into an output queue and

then sending it, possibly at a later time, to the queue for the other service.

A number of messages may build up in the output queue, waiting to be

sent, but they will eventually be sent and sent in the order in which they

were put into the queue.

The advantage of this extra layer of queuing is that the service sending

the message never waits for anything. But the extra layer also introduces

extra overhead. Service Broker will skip the output queue when both services

are on the same instance of SQL Server. In this case, it will put the

message directly into the queue from which the receiving service gets its

messages. Figure 15-3 shows how Service Broker efficiently sends a message

from one service to another

Error Case

1. Broker이 되게 DB가 셋팅 되어 있지 않을 때

• is_broker_enabled, is_trustworthy_on 이 setting 되어 있지 않음.
• 메세지를 전달 하면, Broker 비활성화 되었다고 하고, 받는 쪽 큐에는 대화가 전달되지 않는다.
•  큐의 데이터 전달은 sys.transmission_queue 에 insert 된 후 타켓 큐에 전달 된다.

• 해결
• ALTER DATABASE DBA SET TRUSTWORTHY , ENABLE_BROKER ON
  

  /* 전송큐에대한메세지행포함*/

  select conversation_handle, to_service_name, from_service_name,enqueue_time,

      message_sequence_number,message_type_name,

      is_conversation_error, is_end_of_dialog, message_body ,transmission_status

   from sys.transmission_queue
  
  2.Broker 셋팅 되어질 DB가 master의 owner와 다를 경우
  

  An exception occurred while enqueueing a message in the target queue.

  Error: 33009, State: 2.

  The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DB명'.

  You should correct this situation by resetting the owner of database 'DB명' using the ALTER AUTHORIZATION statement.

  
  해결
  sp_changedbowner 로 변경
  
  3. 상대 큐가 서로 비활 성화 되었을 경우
  로직 적으로 문제가 되서 대화의 시도가 기본값 5 이상 실패하면 큐는 잠기게 된다.
  

  One or more messages could not be delivered to the local service targeted by this dialog.

  
  해결
  ALTER QUEUE 큐명 with status = ON;