8

Once in a while my code encounters

System.Data.SqlClient.SqlException
The service has encountered an error processing your request. Please try again.
Error code 40540. A severe error occurred on the current command.
The results, if any, should be discarded.
Class 20
Number 40197

that happens very rarely, usually goes away in a minute or two and I can't reliably reproduce it. Sometimes Error code can be a number other than 40540.

I've Googled a bit and looks like it's usually triggered by bugs in SQL Server and is reproducible.

I have two options - retry the query or treat it as fatal and break hard. I'd prefer to have better understanding what the problem actually is and whether I'm safe retrying the query.

Do I retry the query when this error occurs?

sharptooth
  • 167,383
  • 100
  • 513
  • 979

2 Answers2

6

A quick google search gave me this.

The service has encountered an error processing your request. Please try again. Error code %d. You will receive this error, when the service is down due to software or hardware upgrades, hardware failures, or any other failover problems. Reconnecting to your SQL Database server will automatically connect you to a healthy copy of your database. You may see error codes 40143 and 40166 embedded within the message of error 40540. The error codes 40143 and 40166 provide additional information about the kind of failover that occurred. Do not modify your application to catch error codes 40143 and 40166. Your application should catch 40540 and try reconnecting to SQL Database until the resources are available and your connection is established again.

You can visit here for more information. It says Your application should catch 40540 and try reconnecting to SQL Database until the resources are available and your connection is established again.

Though on a personal note I would suggest not to use Sql azure as it throttles easily and you can't control the resources allocated to you. What I did was to install sql server on azure vm and use that in my application.

Hope this helps you.

dicemaster
  • 1,203
  • 10
  • 22
  • 1
    I have used SQL Azure a lot with no problems. But if you use it as if it were the same as SQL Server you will have problems. – Craig Feb 11 '13 at 22:14
  • Can you please elaborate on this. I didn't understand what you meant by as if it were the same as SQL Server? – dicemaster Feb 11 '13 at 22:30
  • SQL Azure is designed for scale out, not scale up. The SQL Azure databases are not running on high end servers but virtual machines shared with other users. So with SQL Server you can just fire thousands of queries at it and there will normally not be a problem, with SQL Azure you should not do that. You need to distribute the load over multiple databases to get it to scale. Use Retry on commands. Use caching so you don't need to hit the databases or use Table/Blob storage if possible. All the kind of optimisations that large SQL sites like Stack Overflow use are now applicable to everyone. – Craig Feb 11 '13 at 22:37
  • All you said is fine, but what do you for write intensive app? I can cache the query results, but I will have to hit the db for writes issued. – dicemaster Feb 12 '13 at 07:37
  • @gaurav you might try Cassandra? See this for more details: http://www.datastax.com/docs/0.8/dml/about_writes "Cassandra is optimized for very fast and highly available data writing." – Norman H Oct 29 '13 at 14:44
1

I encountered similar error: And I followed the answer in http://social.msdn.microsoft.com/Forums/en-US/bbe589f8-e0eb-402e-b374-dbc74a089afc/severe-error-in-current-command-during-datareaderread which resolved the issue..

Avoid looping of reader; load data in a DataTable

DataTable table = new DataTable();
table.Load(reader);
reader.Close();
LCJ
  • 22,196
  • 67
  • 260
  • 418