Sqltransaction Has Completed

I have an application which potentially does thousands of inserts to a SQL Server 2005 database. If an insert fails for any reason (foreign key constraint, field length, etc.) the

Solution 1:

Thanks for all the feedback. I've been working with someone from MSFT on the MSDN forums to figure out what's going on. It turns out the issue is due to one of the inserts failing due to a date time conversion problem.

The major problem is the fact that this error shows up if it's a date conversion error. However, if it's another error such as a field being too long it doesn't cause this issue. In both cases I would expect the transaction to still exist so I can call Rollback on it.

I have a full sample program to replicate this issue. If anyone wishes to see it or the exchange with MSFT you can find the thread on MSFT's newsgroups in microsoft.public.dotnet.framework.adonet under the SqlTransaction.ZombieCheck error thread.

Solution 2:

Difficult to help without seeing code. I assume from your description you are using a transaction to commit after every N inserts, which will improve performance vs committing each insert provided N is not too big.

But the downside is: if an insert fails, any other inserts within the current batch of N will be rolled back when you rollback the transaction.

In general you should dispose a transaction before closing the connection (which will rollback the transaction if it hasn't been committed). The usual pattern looks something like the following:

using(SqlConnection connection = ...)
{
    connection.Open();
    using(SqlTransaction transaction = connection.BeginTransaction())
    {
        ... do stuff ...
        transaction.Commit(); // commit if all is successful
    } // transaction.Dispose will be called here and will rollback if not committed
} // connection.Dispose called here

Please post code if you need more help.

Solution 3:

Keep in mind that your application isn't the only participant in the transaction - the SQL Server is involved as well.

The error you quote:

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

doesn't indicate the transaction has comitted, only that it is complete.

My first suggestion is that your server has killed off the transaction because it either took too long (ellapsed wall time) or got too large (too many changes or too many locks).

My second suggestion is to check that you're cleaning up connections and transactions appropriately. It's possible that you're running into problems because you are occasionally exhausting a pool of some resource before things get automatically recycled.

For example, DbConnection implements IDisposable, so you need to ensure you clean up appropriately - with a using statement if you can, or by calling Dispose() directly if you can't. 'DbCommand' is similar, as it also implements IDisposable.

Solution 4:

This exception is thrown because actual DB transaction is already rolled back, so a .NET object representing it on the client side is already a "zombie".

More detailed explanation is here. This post explains how to write a correct transaction rollback code for such scenarios.

Solution 5:

According to this post: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/24/zombie-check-on-transaction-error-this-sqltransaction-has-completed-it-is-no-longer-usable.aspx

A temporary solution could be to try catching the rollback or commit operation. So this code will be enough for stopping the bug to be throwing:

publicstaticvoidTryRollback(this System.Data.IDbTransaction t)
    {
        try
        {
            t.Rollback();
        }
        catch (Exception ex)
        {
            // log error in my case
        }
    }

    publicstaticvoidTryCommit(this System.Data.IDbTransaction t)
    {
        try
        {
            t.Commit();
        }
        catch (Exception ex)
        {
            // log error in my case
        }
    }

Check this example from msdn website: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx