There's another change to the SQL Server database engine in SQL Server 2008 R2. This concerns poison-message handling behavior in Service Broker applications.

Service broker's messaging is always transactional. A RECEIVE SQL statement is transactional and can be combined with other database operations as part of a transaction. For example, you can code a RECEIVE for a Service Broker queue combined with an INSERT of a database row, based on the information in the message. If the transaction fails (say the row's primary key already exists), the message that was RECEIVEd will be put back on the queue. But then, the next RECEIVE could read the same message again; if the message is never able to be processed correctly, and the RECEIVE-INSERT-ERROR-ROLLBACK statement can loop on this particular message. This is known as a poison message.

It is suggested that you write your own poison message handling strategy, a few strategies are listed here. If you do not write your own strategy, Service Broker's default strategy (which happens after five consecutive transaction rollbacks) is to disable the queue. You can receive a QUEUE_DISABLED event if you write a service broker service that handles the BROKER_QUEUE_DISABLED event notification.

Some folks have complained that this is a fairly drastic step (and limits the poison message-handling strategies you can write), but poison message loops do have the capability to waste SQL Server resources. So…

In SQL Server 2008 R2, both the CREATE QUEUE and ALTER QUEUE DDL statements allow the (new) specification POISON_MESSAGE_HANDLING(STATUS = OFF/ON). According to the description "This allows for a custom poison message handing system to be defined by the application." This does not relieve you from writing your own strategy, it just means that you can turn off disabling the queue for a 5-rollback poison message.