"Server failed to resume the transaction" error in SQL Server 2008 + .NET3.5 + LINQ

Question

The aspx.cs Code: the code itself is pretty big, and the code here is fictional, but it looks (the important part) like this:

foreach (Transaction trans in vTransactionList)
        {
            switch (trans)
            {
                case 201: codehere; break;
                case 202: codehere; break;
                case 203: 
                       vProcesso.MarcaEnvioServico(
                                    trans.ProcessId,
                                    trans.CodTrans);
                       break;

            }
        }     

The business class Method:

            RENDataContext db = new RENDataContext();

            Processo update = tabela.SingleOrDefault(
                x => x.CodTrans == pCodTrans);

            update.SentDate= DateTime.Now;
            update.ProcessId = pProcessId;
            update.LogUsuario = pUsuario_Id;
            update.LogVersaoRegistro = servico.LogVersaoRegistro + 1;
            update.LogDataAlteracao = DateTime.Now;

            db.SubmitChanges();

Sometimes (very frequently I get this error (sqlserverexception) when executing this code: "The server failed to resume the transaction." Again, it's just random, sometimes it executes and sometimes it don't. When it fails first it keeps failing for some period of time.

I was using a stored procedure to update the table instead of that LINQ code, and the same problem happened.

Answer 1

You might want to consult this link: server failed to resume transaction. A summary of what I have seen for this error is that multiple commands are being executed with a single connection. The client code then gets confused on the transaction still being in force when according to the SQL Server it has been committed or rolled back. In looking at your code for the business class method I noticed that the context is not in a using statement. The context class should implement IDisposable so Dispose should be called when you are done using the context. You might try changing your code to:

            using(RENDataContext db = new RENDataContext()){

                Processo update = tabela.SingleOrDefault(
                    x => x.CodTrans == pCodTrans);

                update.SentDate= DateTime.Now;
                update.ProcessId = pProcessId;
                update.LogUsuario = pUsuario_Id;
                update.LogVersaoRegistro = servico.LogVersaoRegistro + 1;
                update.LogDataAlteracao = DateTime.Now;

                db.SubmitChanges();
            }

and see if that doesn't fix your problem.