My Adventures in Coding

November 26, 2007

What to do when a Service Broker Queue Appears Stalled

Filed under: SQL Server 2005 — Brian @ 10:21 am

What happens sometimes with a service broker queue, usually when there has been a db failure or some other issue, is you will have a queue that is enabled and activation is still turned on, but is not processing requests.

So you will try and run the following to fix the issue:

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON )

However, this will NOT fix the issue.

The reason is that service broker will see that activation is already turned on for the queue and just do nothing. So what you need to do is first turn activation OFF then turn activation back ON. I know this seems strange, however it seems to be the only way we have been able to restart a queue once we have had a problem occur.

So the fix is to run the following script:

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = OFF )
GO

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON )
GO

November 22, 2007

SQL Server: Service Broker Tips and Tricks

Filed under: SQL Server 2005 — Brian @ 8:31 am

In using Service Broker Queues for the last few months I have come up with a list of very handy frequently used queries.

Is Service Broker Enabled?

SQL Server has a database wide setting that allows all service broker queues in the database to be disabled by setting a single flag. The problem is if a database is moved or restored, this flag is automatically reset to false. There is no way to override this setting. The problem is all messages that are sent to queues while service broker is disabled, will be lost, because this setting disables receiving of messages on all queues.

Check if service broker is enabled

SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDatabaseName'

Enable service broker

--Waits for all open transactions to finish before making change
ALTER DATABASE MyDatabaseName set NEW_BROKER
OR
--Rolls back all other transactions instead of waiting
ALTER DATABASE MyDatabaseName set NEW_BROKER WITH ROLLBACK IMMEDIATE

Those commands work fine on a local machine, but if you need to enable service broker on a database where others have open connections (e.g., query window), you will need the following:

ALTER DATABASE myDatabase set single_user with rollback immediate
GO
ALTER DATABASE myDatabase set new_broker
GO
ALTER DATABASE myDatabase set multi_user

Activate/Enable a Queue

Activation on a queue allows the attached stored procedure to be run anytime a new message is inserted into the queue. (Note: I am also ensuring the queue is enabled in this update statement). If a queue is not enabled, it will not accept messages, so any messages sent to the queue will be lost!

Enable activation on the queue to process messages

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON )

Disable activation on the queue to stop processing messages

ALTER QUEUE dbo.MyQueue
WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = OFF )

Service Broker General Queue Information

This query gives you all information about each queue in the database. Use this query to find out if a queue is enabled, if activation is enabled on the queue, etc.

select * from sys.service_queues

Viewing Messages on a Queue

If you need to view the body of messages on a service broker queue, you can use this query to return the XML content of each message on the queue.

SELECT cast(Message_Body as XML)
FROM dbo.QueueName with(nolock

Count Messages on a Queue

You can use “Select count * from dbo.QueueName”, however that is slow, especially if there are thousands of messages presently on the queue. It is much faster to use the message count that service broker stores on each queue.

select p.rows
from sys.objects as o
join sys.partitions as p on p.object_id = o.object_id
join sys.objects as q on o.parent_object_id = q.object_id
where q.name = 'queuename'
and p.index_id = 1

Get the SPID of a Running Activation Sproc

When an acitvation sproc is running on prod and causing problems, it can be difficult to figure out which SPID is associated with that sproc when we need to kill that process. The following query makes that easy to find:

select * from sys.dm_broker_activated_tasks

Find out of a conversation has been ended

If you ever get this error message, then you have a transaction that has not been ended.

Msg 8429, Level 16, State 1, Line 46
The conversation endpoint is not in a valid state for SEND. The current endpoint state is ‘DI’.

DI means a “Disconnected Inbound” which means the other end of the conversation has been ended with an END CONVERSATION command, however this side of the conversation has been left open. To check for unended conversations use the following command:

select * from sys.conversation_endpoints

To close the conversation get the conversation_handle from the above command then run an END CONVERSATION on that conversation handle. Here is an example:

END CONVERSATION '73645350-72C4-DC11-9374-0003FF3A5C87'

Useful Resources

How to troubleshoot service broker problems

Blog at WordPress.com.