Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In our application we're using TransactionScope's. We're aiming to not use the MSDTC service because it's a lot slower than lightweight transactions.

using (var transactionScope = new TransactionScope())
{
  ...
  transactionScope.Complete();
}

While connected to the test database we need no MSDTC but the exact same code, executed on the same machine, escalates to use MSDTC when connected to the production or development database.

The development database is not clustered and isn't a named instance.

All are SQL Server 2008 (10.50.2500) and the connection strings are exactly the same except for the hostnames.

It seems there has to be some difference in the setup of the databases. Any ideas on what that could be?

EDIT

The transaction was there to protect inserts in two different tables. I noticed that there was also two calls that resulted in two selects in there. There was no need for these to be in the transaction so I moved them outside of it. The need for MSDTC then disappeared in all three databases. Problem solved but without really knowing what caused it.

share|improve this question

migrated from stackoverflow.com Mar 3 '12 at 1:03

This question came from our site for professional and enthusiast programmers.

    
Does this happen when you have a single connection or more than one? If more than one, are they different connection strings? –  Oded Mar 2 '12 at 10:14
    
In this case there are multiple connections using the same connection string. I will have to test how single connection transactions behave. –  Jonas Elfström Mar 2 '12 at 10:20
    
Is the production server set to be backwards compatible with SQL Server 2005? If so, that might be the issue, as 2005 had a bug where it would escalate to distributed whenever more than one connection was part of the transaction. –  Oded Mar 2 '12 at 10:22
    
"Compability level: SQL Server 2008 (100)" for both. –  Jonas Elfström Mar 2 '12 at 10:26
    
Are you using MARS? msdn.microsoft.com/en-us/library/ms131686.aspx –  RobS Mar 2 '12 at 14:13

2 Answers 2

up vote 3 down vote accepted

Here are some of the things you can try to get it to work:

  • Compare Enlist=false in your connection strings
  • Compare MultipleActiveResultSets=true in your connection strings
  • User instance databases appear to work differently for some reason - we couldn't get it to work consistently with them - use full databases all of the time
  • You can reuse the same connection (close/reopen), or use more than one connection in a TransactionScope, but you cannot use more than one connection at the same time. You CAN however keep a ThreadStatic connection and pass that around (or, as we did, use ThreadLocal<> in a db wrapper class) - this solved a concurrency problem for us in ASP.NET which was causing unnecessary escalation
  • Don't bother if your db < 2008 or if compatibility level < 100
  • When using connection strings, don't use the ConnectionString property of another connection to create your new connection - it may not be the same as the original, which will cause escalation
  • Keep your connections open only for as long as you need to, close them at your earliest opportunity.

Stuff I found useful:

share|improve this answer

In this case there are multiple connections using the same connection string

Irrelevant. Second connection = propagation to MSDTC. All you can do is not open multiple connections. Stick to ne connection and it will not propagate to MSDTC. Open a second one - same string, even if the first is closed - and it must propagate as it can not / does not reasign the conenction to the new one. That makes it 2 conenctions which is per definition a distributed transaction.

share|improve this answer
    
That was true for SQL Server 2005 but I've been told that SQL Server 2008 solved that problem. Unfortunately for us, it only works to one of three servers. Even though it only works on one of three server it proves your answer wrong. petermeinl.wordpress.com/2011/03/13/… –  Jonas Elfström Mar 2 '12 at 13:18
    
Well, it is the only i have so far and there are tons of similar answers around in google. I would love to be prooven wrongt here - makes conenction maangement a LOT easier. –  TomTom Mar 2 '12 at 13:37
    
I moved two selects outside of the transaction and only kept the two inserts (in separate connections). Now it works on all three database servers without any need for MSDTC. –  Jonas Elfström Mar 2 '12 at 14:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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