Error - The transaction associated with the current connection has completed, but has not been deleted.

Question

I'm having trouble using TransactionScope to transfer multiple database queries into a transaction. I am using SqlBulkCopy with batchsize 500. When I increase the batch size to 1000, I get an error message:

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

This is the code I'm 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

I know this is late, but maybe it will help someone else.

This can happen when the transaction expires. You can increase the timeout for your transaction like this (use values ​​corresponding to the expected length of your transaction). Code below for 15 minutes:

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

That's why it could work on batchsize 500, not 1000.

Answer 2

I found that setting a timeout in TransactionScope does not work for me. I also needed to add the following configuration key to the end of the machine.config <configuration> in order to skip the default maximum timeout for 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(); }