Geeks With Blogs

News My Blog has been moved to https://mfreidge.wordpress.com
Michael Freidgeim's Blog My Blog has been moved to https://mfreidge.wordpress.com

Our system started to get intermittent errors like the foolowing in the code that was NOT changed recently:

System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Rollback()

I was pointed to MS Kb http://support.microsoft.com/kb/912732, that I've installed, but it didn't change -errors still appeared.

Unfortunately, the KB doesn't have a list of changed files and their versions, so I can't say for sure that my install was successful.

The same issue reported in different places: http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic21204.aspx , http://support.citrix.com/forums/thread.jspa?forumID=115&threadID=93236 ;http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic37219.aspx

In  the thread SQL Server Raiserror and .NET SqlDataAdapter noted ,that the error ould be result of extra commit/Rollbcks in SQL trigger that uses @@ROWCOUNT > 0 .

Some people(see here) had this problem back in 2003 and MS dona fix for them. Why we have the same errors now?

The post "SQL Server, ADO.NET and Nested Transactions" points that SQL Server setting SET XACT_ABORT ON can cause the errors.
But the setting of SET XACT_ABORT is by default OFF and can be set at execute or run time ,   can't be specified on Server/Database level.

The MSDN SqlTransaction.Rollback Method () documentation has a good example , showing that in a catch block, error should be logged first, and then call Rollback in another Try block.(see also  this Microsoft Knowledgebase entry).

Finally I understood that Rollback is NOT required when using block is specified, because "The transaction is rolled back in the event it is disposed before Commit or Rollback is called."
It is confirmed in  Community Content in http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx and in CodeProject Easier Database Transactions - Extending the Using Statement to Perform Automatic Database Transactions,

We will need to change our code to get rid of Rollback() in catch sections,when using block is specified.It will help to see the actual error causing exception.

Update: After removing explicit calls to Rollback we found that tte most of errors were caused by timeouts in access to database. It is still required to solve these errors, but it is another story.


 

Posted on Monday, September 3, 2007 1:08 AM .Net Framework , SQL Server | Back to top


Comments on this post: "This SqlTransaction has completed; it is no longer usable." errors

# re: "This SqlTransaction has completed; it is no longer usable."
Requesting Gravatar...
Good article, the author thanks!
Left by 北京租车 on Sep 13, 2007 5:56 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Very much informative.
Left by Nabeel Khan on Oct 13, 2008 5:22 PM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
To investigate timed out exceptions, use SQL Server Profiler and SQL Server Activity Monitor to find which queries block the others and cause timeouts.
Left by Michael Freidgeim on Feb 25, 2009 4:14 PM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Muy bueno! Tenia el mismo problema y gracias a lo que explicas entendí todo! Gracias.
Left by Carlos on Jul 17, 2009 11:16 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Nice post,

You saved my day, it was the first time I see this error

Thanks
Left by web development company on Aug 17, 2009 10:49 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
i have no idea wat any of that means or how to fix my problem!
Left by kellie on Aug 22, 2009 2:15 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Hey , Information is good but error still there.

how to fix this error?

thanks
chetan.
Left by chetan on Mar 06, 2010 1:17 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Hi!

"Easier Database Transactions - Extending the Using Statement to Perform Automatic Database Transactions" Codeproject Article changed to the following URL:

http://www.codeproject.com/KB/database/EasyTransactions.aspx
Left by Pedro Martins on May 31, 2010 4:49 PM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Pedro Martins,
Thanks, link fixed.
Left by Michael Freidgeim on Jun 01, 2010 5:06 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Wow. Thanks.
Left by Chateau France on Nov 11, 2010 8:12 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Thanks . we have rectified the error.
Left by Hiru on Mar 03, 2011 2:51 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
Really good answer thx a lot ....
Left by Prageeth on May 19, 2011 7:01 AM

# re: "This SqlTransaction has completed; it is no longer usable." errors
Requesting Gravatar...
fixed through increasing the command timeout.
Left by fracis on Jul 05, 2011 2:33 AM

Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net