Why does a SqlConnection get closed mid-transaction?

Question

In code that simplified looks something like the following, conn2.Open(), executed less than 1 second after the transaction was started (i.e. no timeout issue) sometimes throws, claiming the (ambient) transaction had already been aborted - in other words, conn2 is not the problem.

using (var ts1 = new TransactionScope(...))
{
    using (SqlConnection conn1 = new SqlConnection(connStr1))
    {
        conn1.Open();
        var cmd1 = conn1.CreateCommand();
        // use cmd1 .. 
    }

    using (SqlConnection conn2 = new SqlConnection(connStr2))
    {
        conn2.Open(); // THIS SOMETIMES THROWS
        // ...
    }

    ts1.Complete();
}

Every time this exception has occurred so far, the logs indicate that between the failing transaction and the last transaction before it there are at least 4.5 long minutes without any transaction, so it looks like perhaps a TCP connection could have timed out.

But if e.g. conn1 had timed out, it would already throw on conn1.Open(). Instead it throws on conn2.Open(), indicating that conn1 had been closed.

So what happened to conn1 and why? And why is it only indicated when calling conn2.Open()?

When trying to reproduce the problem with above code, by manually killing the TCP connection underlying conn1 after calling conn1.Dispose(), I can reproduce almost the exact same stack trace occurring on conn2.Open(). Only the InvalidOperationException turns into a System.Data.SqlClient.SqlException, everything else is 100% identical. But between the activity in conn1 and the successful conn1.Dispose() and conn2.Open() almost no time elapses, so it cannot be a timeout.

This is on

• WindowsServer 2008R2
• .Net 3.5
• SQLServer 2008R2

---

The exception and stack trace:

System.Transactions.TransactionAbortedException: The transaction has aborted.
 ---> System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction.
 ---> System.InvalidOperationException: The requested operation cannot be completed because the connection has been broken.
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
   at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
   at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx)
   at System.Transactions.Transaction.Promote()
   at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
   at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
   at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at My.Code...

---

Edit (addressing a proposed answer)

Can it be a timeout? As I mentioned, the error occurs less than 1 sec into the transaction, so (absent some weird bug) it cannot be a mereTransaction/TransactionScope/DTC timeout (which are all set north of 30 seconds).

Fwiw, this is what a DTC timeout after conn1.Dispose() and before conn2.Open would have looked like (on a Japanese language OS).

System.Transactions.TransactionException: トランザクションの状態に対して操作が有効ではありません。
 ---> System.TimeoutException: トランザクションがタイムアウトしました。
   --- 内部例外スタック トレースの終わり ---
   場所 System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
   場所 System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   場所 System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   場所 System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   場所 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   場所 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   場所 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   場所 System.Data.SqlClient.SqlConnection.Open()

Answer 1

It seems to be a problem with your DTC Timeout which is timed out at the moment you are stating the second connection and promote the transaction to a dtc transaction. You can change the time out in the machine settings. You change the timeout for all DTC transaction, so you can have a performance impact when changing it to a large value.

A 10 min timeout in machine.config:

<configuration>
 <system.transactions>
   <machineSettings maxTimeout="00:10:00" /> 
 </system.transactions>
</configuration>