Linq-To-Sql and MARS woes - A severe error occurre

Question

We have built a website based on the design of the Kigg project on CodePlex:

http://kigg.codeplex.com/releases/view/28200

Basically, the code uses the repository pattern, with a repository implementation based on Linq-To-Sql. Full source code can be found at the link above.

The site has been running for some time now and just about a year ago we started to get errors like:

• There is already an open DataReader associated with this Command which must be closed first.
• ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

These are the closest error examples I can find based on my memory. These errors started to occur when the site traffic started to pick up. After banging my head against the wall, I figured out assumed that the problem is inherit within Linq-To-Sql and how we are using the same connection to call multiple commands in a single web request.

Evenually, I discovered MARS (Multiple Active Result Sets) and added that to the data context's connection string and like magic, all of my errors went away.

Now, fast forward about 1 year and the site traffic has increased tremendously. Every week or so, I will get an error in SQL Server that reads:

A severe error occurred on the current command. The results, if any, should be discarded

Immediately after this error, I receive hundreds to thousands of InvalidCastException errors in the error logs. Basically, this error shows up for each and every call to the Linq-To-Sql data context. Only after I restart the web server do these errors clear up.

I read a post on the Micosoft Support site that descrived my problem (minus the InvalidCastException errors) and stating the solution is that if I'm going to use MARS that I should also use Asncronous Processing=True. I tried this, but it did not solve my problem either.

Not really sure where to go from here. Hopefully someone here has seen and solved this problem before.

Answer 1

So after much refactoring and re-architecting, we figured out that problem all along is MARS (Multiple Active Result Sets) itself. Not sure why or what happens exactly but MARS somehow gets result sets mixed up and doesn't recover until the web app is restarted.

We removed MARS and the errors stopped.

If I remember correctly, we added MARS to solve the problem where a connection/command was already closed using LinqToSql and we tried to access an object graph that hadn't been loaded. Without MARS, we'd get an error. But when we added MARS, it seemed to not care about it. This is really a great example of us not really understanding what the heck we were doing and we learned some valuable (and expensive) lessons from this.

Hope this helps others who have experienced this.

Thanks to all how have contributed their comments and answers.

Answer 2

I understand you figured out the solution..

Following is not a direct solution to the problem; but it is good for others to take a look at

1. What does "A severe error occurred on the current command. The results, if any, should be discarded." SQL Azure error mean?

2. http://social.msdn.microsoft.com/Forums/en-US/bbe589f8-e0eb-402e-b374-dbc74a089afc/severe-error-in-current-command-during-datareaderread

Answer 3

I have the same issue. Once the errors start, I have to restart the IIS Application Pool to fix.

I have not been able to reproduce the bug in dev despite trying many different scenarios involving multi-threading, leaving connections open, etc etc.

One possible lead I do have is that amongst the errors in the server Event Log is an OutOfMemoryException for the Application Pool. Perhaps this is the underlying cause of the spurious SQL Datareader errors (a memory leak elsewhere). Although again I haven't been able to reproduce this in dev.

Obviously if you are using a 64 bit OS then this is probably not the cause in your case.