7

I have a .NET 4.0 database-centric application (thick client, WinForms) running against SQL Server (mostly via Linq to Sql with some vanilla ADO.NET sprinkled in) that is deployed in hundreds of environments (each maintained by our customers' IT staff). The application is a rewrite of my company's legacy FoxPro product which was in use for many years.

The application seems to be working fine for all of our customers except one, which is having intermittent exceptions like so:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.

Everything I can find on the web about this (such as here and here) say this problem is a SQL Server 2005 bug that was resolved in SP1. However, this customer is using SQL Server 2008 R2 Express (this has been verified by our company; we aren't just taking the customer's word for it). Additionally, we are not using connection pooling or distributed transactions, so the links I found seem even less relevant.

Again, the problems are intermittent (a few times per day), and occur on all of their various workstations (seeming to indicate that it is not an issue with a single client). The legacy FoxPro application is still present in their environment and works fine (against the exact same database), which would seem to indicate that it is not solely a network or database server issue.

Any help would be greatly appreciated.

Community
  • 1
  • 1
Pennidren
  • 111
  • 1
  • 5
  • Deadlocks? [SQL Server Profiler](http://msdn.microsoft.com/en-us/library/ms181091(v=sql.105).aspx) may help pin it down. – HABO Aug 03 '12 at 15:47
  • Perhaps, but why the poor exception message, then? And why aren't any other customers reporting the same? – Pennidren Aug 03 '12 at 15:58
  • 2
    You're asking about the quality of a diagnostic message provided by the folks who brought you `ERROR_SUCCESS`? You may find some troubleshooting tips for timeouts [here](http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation). Not sure about the "batch is aborted" piece. – HABO Aug 03 '12 at 17:20

1 Answers1

4

These errors indicate that the CommandTimeout is reached (default is 30 seconds) before the server returns results. This does not indicate any bug and no SP can solve it. This is a performance problem (most likely missing indexes) and must be investigated as a performance problem. Try following the SQLCAT performance troubleshooting flowchart.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Proper and thorough indexes are present. Performance is not a problem for hundreds of other installations. Some queries which have failed are fairly trivial. There may be some performance issue particular to this installation, but we would expect problems to then occur with the legacy application as well. I understand that the first portion of the exception mentions a timeout and we have solved plenty of other timeout related exceptions in the past where performance was an issue. I believe this is different. Please see the latter portion of the exception message. What could cause that? – Pennidren Aug 03 '12 at 22:52
  • The latter portion explain what caused this: `abort signal sent from client`. Ie. the CommandTimeout was reached. – Remus Rusanu Aug 04 '12 at 07:11
  • In the past when we have gotten timeout exceptions, the latter portion of the message has not been present, only the former. I still think the additional part of the message is the key factor here, but I will investigate from a performance standpoint (it isn't like I have an alternative, anyway). Thanks for the quick replies. – Pennidren Aug 05 '12 at 02:51