I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition.

.NET errors show a problem reported from SqlNotificationInfo Enumeration (http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlnotificationinfo(v=vs.80).aspx ) as: Event Reason: Isolation.

During tracing I notice that the audit login event immediately preceding the Broker conversation shows:

 -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read uncommitted

So, my working theory is that the transaction isolation level used for the conversation is not supported. But where is this set and how can I change it?

I've read about a similar problem here:

http://www.roelvanlisdonk.nl/?p=3144 and


  • Don't understand why most connection are being made as read committed but these particular connections are read uncomitted – reticentKoala Feb 19 '13 at 16:55
  • You or a developer will need to debug the application to figure out where the isolation level is being set on the connection being used to run the query. – Jon Seigel Feb 19 '13 at 17:57
  • It's the same application, same connection string, hence the confusion. – reticentKoala Feb 20 '13 at 14:54

The explanation to this problem turned out to be an issue with another part of the application explicitly opening read uncommitted connections. These read uncommitted connections were then left in the connection pool and reused by service broker, which can't operate using this isolation level.

The solution was simple: Make sure service broker is connecting with a different connection string, thereby using a different connection pool.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.