Alter queue with activation service broker

We have already experiencing deadlock issues caused by these two stored procedures. Asynchronous execution is my main goal with this implementation.

Is there a way to use one target queue for both stored procedures to prevent any chance of deadlocks? Is there anything I can do to make it more reliable?

You can and you should. Send, to the same service, two different message types for the two operations you desire. The activated procedure should then execute logic for Add or logic for Update, depending on message type. SSB activation will be very reliable, that's not going to be a problem. To achieve a high throughput processing, you will have to dequeue and process in batches TOP into table variables.

See Exception handling and nested transactions for a pattern that can be applied to process a batch of messages. You'll need to read and understand Conversation Group Locks.

No need to, SSB activation will retry for you. As you rollback, the dequeue RECEIVE will rollback thus making the messages again available for activation, and the procedure will automatically retry. Note that 5 rollbacks in a row will trigger the poison message trap. If 1 cannot handle the load, add more. As long as you understand proper conversation group locking, the parallel activated procedures should handle unrelated business items and never deadlock.

If you encounter deadlocks between instances of activated procedure on the same queue, it means you have a flaw in the conversation group logic and you allow messages seen by SSB as uncorrelated different groups to modify the same database records same business entities and lead to deadlocks. BTW, you must have an activated procedure on the initiator service queue as well. See How to prevent conversation endpoint leaks.

When the queue is unavailable, no messages can be added to the queue or removed from the queue. This allows you to retain messages for auditing purposes, or to perform compensating transactions if an error occurs. This setting should only be used if required to meet the service level agreement for the application. Rebuilds all indexes on the queue internal table.

Use this capability when you are experiencing fragmentation problems due to high load. Reorganize all indexes on the queue internal table.

However, these numbers are only for general guidance as a starting point for your environment. To determine the amount of index fragmentation, use sys. Moves the queue internal table with its indexes to a user-specified filegroup. The new filegroup must not be read-only. This value must be a SQL Server identifier. SQL Server must be able to check the permissions for this user at the time that the queue activates the stored procedure. For Windows domain user, the SQL Server must be connected to the domain and able to validate the permissions of the specified user when the procedure is activated or activation fails.

For a SQL Server user, the server can always check permissions. SELF Specifies that the stored procedure executes as the current user. Specifies whether Service Broker starts the stored procedure. If this clause is not specified, the default is ON. Specifies the name of the stored procedure to start to process messages in this queue. This value must be a SQL Server identifier. Specifies the maximum number of instances of the activation stored procedure that the queue starts at the same time.

Specifies the SQL Server database user account under which the activation stored procedure runs. SQL Server must be able to check the permissions for this user at the time that the queue starts the stored procedure. For a domain user, the server must be connected to the domain when the procedure is started or activation fails.

For a SQL Server user, the server can always check permissions. SELF Specifies that the stored procedure executes as the current user. A queue that has poison message handling set to OFF will not be disabled after five consecutive transaction rollbacks.

This allows for a custom poison message handing system to be defined by the application. Specifies the SQL Server filegroup on which to create this queue. You can use the filegroup parameter to identify a filegroup, or use the DEFAULT identifier to use the default filegroup for the service broker database. When no filegroup is specified, the queue uses the default filegroup for the database.

A queue might not be a temporary object. Therefore, queue names starting with are not valid. Creating a queue in an inactive state lets you get the infrastructure in place for a service before allowing messages to be received on the queue.

Service Broker does not stop activation stored procedures when there are no messages on the queue. An activation stored procedure should exit when no messages are available on the queue for a short time.