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.