This SqlTransaction has completed; it is no longer usable.

• spencer-682903

  SSC Veteran

  Points: 217

  More actions

  November 17, 2009 at 10:54 pm

  #213911

  Hi

  I am getting the following error trying to commit transaction - This SqlTransaction has completed; it is no longer usable. The transaction involves saving a quite large object and its associated entities. The entire transaction involves multiple select and insert statements as well potentially a couple of updates. The code runs fine up until the point where the transaction is commited at which point the following exception is thrown

  System.Data.SqlClient.SqlException was caught

  Message="The transaction operation cannot be performed because there are pending requests working on this transaction."

  Source=".Net SqlClient Data Provider"

  ErrorCode=-2146232060

  The transaction object involved contains the following

  'dbTrans.transaction.IsolationLevel' threw an exception of type 'System.InvalidOperationException'

  This SqlTransaction has completed; it is no longer usable.

  At the point prior to the commit statement, the connection is open and the transaction appears to be valid. I've gone through my code and I'm certain there is only one connection and one transaction object is created during the process.

  Running the exact same code not wrapped in a transaction works fine, so this is probably not a problem with the database. I've also run DBCC CHECKDB command to verify this. SQL server profiler and logs also do not appear to yield any clues.

  We are running SQL Server 9.0.3175 if that helps at all.

  I'm no DBA, just a mere developer with only enough database knowledge to get by, so I hoping one of you serious SQL Server guys can help me out with some pointers on how to track this problem down!

  Thanks in advance

  Spencer

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  November 18, 2009 at 3:10 am

  #1080763

  It might not be the right answer, but check your code for DDL operations inside of TRY/CATCH blocks. Even if your code runs perfectly without a transaction when it gets into the catch block, it won't run inside a transaction, because failure of DDL instructions makes the transaction non committable.

  I don't remember if this is the exact error message associated with this scenario, but it could be something to investigate.

  Regards

  Gianluca

  -- Gianluca Sartori

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  November 18, 2009 at 10:05 am

  #1081037

  Gianluca Sartori (11/18/2009)

  ---

  It might not be the right answer, but check your code for DDL operations inside of TRY/CATCH blocks. Even if your code runs perfectly without a transaction when it gets into the catch block, it won't run inside a transaction, because failure of DDL instructions makes the transaction non committable.

  I don't remember if this is the exact error message associated with this scenario, but it could be something to investigate.

  Regards

  Gianluca

  This problem relates to either bad code or existing problem in SQL Server 2005 and below, if transactions are complex it is best to start with System.Transaction code passed to sqltransaction code which is converted to RDBMS transactions. But developers use short cuts so SQL Server and Oracle gets confused and end long running transactions. But ANSI SQL added transaction nesting at the point of connection which Microsoft implemented in SQL Server 2008 and VS2008. So either do more design work or upgrade to resolve this issue.

  Kind regards,
  Gift Peddie