Error - The transaction associated with the current connection has completed but has not been disposed

Question

I've been having trouble using the TransactionScope to wrap multiple database queries into a transaction, I am using SqlBulkCopy with batchsize 500. When I increased the batch size to 1000 I am getting the error:

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

This is the code I am using:

using (var scope = new TransactionScope())
{
    using (var connection = (SqlConnection)customerTable.OpenConnection())
    {
        var table1BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName1
        };

        table1BulkCopy.WriteToServer(table1DataTable);

        var table2BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName2
        };

        table2BulkCopy.WriteToServer(table2DataTable);

        var table3BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName3
        };

        table1BulkCopy.WriteToServer(table3DataTable);

        var table4BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName4
        };

        table4BulkCopy.WriteToServer(table4DataTable);

        scope.Complete();
    }
}

Answer 1

This can happen when the transaction times out. You can increase the timeout for your transaction like this (use values appropriate for the expected length of your transaction). The code below is for 15 minutes:

using (TransactionScope scope = 
             new TransactionScope(TransactionScopeOption.Required, 
                                   new System.TimeSpan(0, 15, 0)))
  {
      // working code here
  }

This is why it could have worked for batchsize 500 and not for 1000.

Answer 2

I found that setting the timeout in the TransactionScope didn't work for me. I also needed to add the following config key to the end of the machine.config <configuration> tag to extend past the default max timeout of 10 minutes.

<system.transactions>
    <machineSettings maxTimeout="00:30:00" /> <!-- 30 minutes -->
</system.transactions>

Credit: http://thecodesaysitall.blogspot.com.au/2012/04/long-running-systemtransactions.html

Answer 3

Move scope.Complete(); outside the connection block.

using (var scope = new TransactionScope())
{
  using (var connection = (SqlConnection)customerTable.OpenConnection())
   {
    //
   }
  scope.Complete();
}

Answer 4

Pretty obvious problem with time out, but you don't get effect if you set TransactionOptions.Timeout higher. Even if you set TimeSpan.MaxValue you don't actually get a profit. It doesnt matter that the Timeout property of the TransactionOptions are set to a higher value, TransactionOptions.Timeout can not exceed maxTimeout property. You should set up some changes in machine.config.

Shortly you should find machine.config file %windir%\Microsoft.NET\Framework\yourversion\config\machine.config
And add this in <configuration> tag:

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

Here you can set maxTimeout property to 30 minutes.
See following for more details http://thecodesaysitall.blogspot.com/2012/04/long-running-systemtransactions.html

Answer 5

The full answer must be more full.

You must specify - where will be max transaction timeout determined - in the .Net code, or in the server config

<sectionGroup name="system.transactions".... 
    ...allowDefinition="MachineOnly"
</sectionGroup>

In this case you can set max timeout in the machine.config

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

Or may be you want to override this behaivor in the application. Then in the machine.config you should set atribute the velue:

...allowDefinition="MachineToApplication"

This is a good arcticle: https://blogs.msdn.microsoft.com/ajit/2008/06/18/override-the-system-transactions-default-timeout-of-10-minutes-in-the-code/

Answer 6

Apply this :

TransactionScope(TransactionScopeOption.Required, new System.TimeSpan(0, 30, 0));
(0, 30, 0) = (hour,minute,seconds)

If you want to test, apply the Time Span with this (0, 0, 1), try and you will get the error. After that, increase seconds or minute or hour and try. it will work.

Answer 7

C# 9 Lang version. TransactionScopeOption.Suppress works like magic for me.

// TransactionScopeOption.Suppress works and fixed my issue
using TransactionScope Scope = new TransactionScope(TransactionScopeOption.Suppress);
try
{
    using var CentralConnection = SQLConnection("Other connection string here");
    using var LocalConnection = SQLConnection("Other connection string here");

    // Central
    using var cmd0 = new SqlCommand("OtherSQLCommandTextHere", CentralConnection)
    {
         CommandTimeout = 0 // I just add zero timeout here
    };
    cmd0.ExecuteNonQuery();

    // Local
    using var cmd1 = new SqlCommand("OtherSQLCommandTextHere", LocalConnection)
    {
         CommandTimeout = 0 // I just add zero timeout here
    };
    cmd1.ExecuteNonQuery();

    // The Complete method commits the transaction. If an exception has been thrown,
    // Complete is not called and the transaction is rolled back.
    Scope.Complete();
}
catch (Exception ex)
{
    Scope.Dispose();
    MessageBox.Show(ex.ToString());
}

public SqlConnection SQLConnection(string ISQLConnection)
{
     SqlConnection conn = null;
     try
     {
         conn = new SqlConnection(ISQLConnection);
         conn.Open();            
     }
     catch 
     {
         // skipping here error message
     }
     return conn;
 }