Databases
StackOverflow2013
Postsdbo
POWhy (on a specific server) would ADO,NET get a Time Out exception after db result is returned and the reader is being closed?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy (on a specific server) would ADO,NET get a Time Out exception after db result is returned and the reader is being closed?
    primarykey
    data
    text
    <p>I have some strange behavior occurring in an ASP.NET application that I am trying to fix.</p> <p>I am getting the following error from code: </p> <p><em>Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</em></p> <p>I am familiar with this error and many of it's causes. All of my typical avenues of trouble shooting have failed.</p> <p>Here are some of the dynamics:</p> <p>The server was recently re-built. So it could be a server configuration issue.</p> <p>This error only happens on a specific web server. When I run the application it locally, and from other servers it is fast. I cannot re-pro perf issues with the proc in MSSMS from my machine.</p> <ul> <li>This tells me that it is specific to this server. </li> </ul> <p>When I run The same proc using the OSql command--line utility It works. Fast.</p> <ul> <li>This indicates that it is likely something .NET related, NOT db related</li> </ul> <p>Prior to this code executing, I have executed other Stored procedures on this server, and on this same DB.</p> <ul> <li>This suggests the proc may be related , but is not a "server X cannot talk to server Y" </li> </ul> <p>I have gotten confirmation From DB owners that the DB has received the command, executed (>1second )it and returned the data.</p> <ul> <li>By tracing the code, I see the results are returned and it is not until I try to close the data reader that the error occurs. </li> <li>In fact I see it takes 36 milliseconds to execute the stored procedure and iterate through the result.</li> </ul> <p>It looks like the call to DataReader.Close is what is taking time, and eventually timing out.</p> <p>I have increased the max Pool from 31 to 100. </p> <p>Here is a sample of what my code looks like, how it is structured. I have been hacking at it for trouble shooting: I have added the explict close to ensure I know where the error occurs. There may be syntax issues: I have made it generic, and may have introduced bugs in doing so. </p> <pre><code> public double GetMyData() { double returnValue; // Used in logging to see if code was reached &amp; how long it took. System.Diagnostics.Stopwatch s = new System.Diagnostics.Stopwatch(); using (SqlConnection cn = Connections.GetSqlConnection()) { cn.Open(); using (SqlCommand cmd = getSQLCommmand("SomeProcName")) { Log.Log.WriteTrace(string.Format("Execute {0} ","SomeProcName"),0); s.Start(); SqlDataReader dr= null; try { dr = cmd.ExecuteReader(); s.Stop(); Log.Log.WriteTrace("Timer", "ExecuteReader done " + s.ElapsedMilliseconds + "ms ", 0); s.Start(); if (dr != null) { if (dr.Read()) { returnValue = Conversion.DBNullToDouble( dr[0]); } s.Stop(); Log.Log.WriteTrace("Timer", "dr.read done (result:" + returnValue + ")" + s.ElapsedMilliseconds + "ms ", 0); // I get Here } }catch(Exception ex) { Log.Log.PersistException(ex); } //} if(dr!=null &amp;&amp; !dr.IsClosed) dr.Close();// This times out if (cn != null &amp;&amp; cn.State !=ConnectionState.Closed) cn.Close(); Log.Log.WriteTrace("DONE "), ; } } return (returnValue); } </code></pre> <p><strong>UPDATE</strong></p> <p><code>dr.Close();</code> takes 2 minutes to execute. Just on this server. Locally it takes less than a second.</p> <p><strong>UPDATE</strong></p> <p>Per the accepted answer's comments: I have a proc that has multiple records. I am taking the fist one. Calling cmd.Cancel() has not fixed, but has drastically reduced the time taken to close the data reader. Exploring this should help me fix the problem. I do not know why this is only happening on this server, as the server is a dev server. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbrian chandley
    1. USbrian chandley
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COhave you tried to use it as well in a using statement?
      singulars
      1. POWhy (on a specific server) would ADO,NET get a Time Out exception after db result is returned and the reader is being closed?
      1. USMr.
    2. COThanks for your comment. I have, The original code relied on a Using statement to manage closing the reader and the connection. I changed it because the Error was occurring in the IL emitted by the compiler. The source code throwing the error was '}'. I converted this to explicit close to make sure there was not an error being hidden.
      singulars
      1. POWhy (on a specific server) would ADO,NET get a Time Out exception after db result is returned and the reader is being closed?
      1. USbrian chandley
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload