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