Periodic InvalidCastException and "The server failed to resume the transaction" with Linq

Question

I see this on our stage system, after it has been up for 2-3 days.

"The server failed to resume the transaction. Desc:39000000ef." (with desc:xxx increasing every time).

The stack trace shows

System.Data.SqlClient.SqlException: The server failed to resume the transaction. Desc:39000000ef.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at ...

After this has happened the entire server goes to heck. It seems like the metadata is no longer correctly represented in memory, because I see many InvalidCastExceptions afterwards. I'm guessing this is due to the system trying to deserialize a string into an int field because the metadata is offset incorrectly.

i.e.

System.InvalidCastException: Specified cast is not valid.
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at Read_Order(ObjectMaterializer`1 )
at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

What does this mean?

I'm fairly sure the database wasn't updated behind the systems back, and the database was online the entire time.

The problem persists from here on out, until the server is restarted, after which everything works nicely.

Do I need to have code that re-establishes database connection if it fails? Doesn't the framework handle this by itself?

I'm using Sql server 2008, IIS 6, and .Net 3.5SP1

UPDATE: The code structure does something like this:

var dc = new datacontext()
IList<someobject> objs = dc.GetAllItemsToProcess()
var dc2 = new datacontext();
 foreach( var o in objs ) {
    try {
         var o2 = dc2.someobjects.SingleOrDefault(x=>x.id = o.id);
          // do stuff to o2
         dc2.save();
   } catch() {
          // something failed so restart datacontext()
         dc2 = new datacontext();
    }
}

Answer 1

This is likely not a problem with your code. It is a bug in SQL Server. They had a similar problem in SQL Server 2005. It only happened under conditions that were just right, so very few people ever saw it and those that did were very confused.

Having said that, here are some things to check that have worked for others with the same problem:

• Look for DataReaders that aren't closed. Make sure you're doing myReader.Close() after you read the rows you want. Many people just rock on without closing.
• Use the native SqlTransaction class instead of OleDbTransactions wherever possible.
• Look at your transactions. Make sure you're committing/rolling back cleanly before you close your Connection.
• Use Connection.BeginTransation rather than Connection.BeginDbTransaction