locked
SOLVED:SqlException (0x80131904): Timeout expired. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User921007156 posted

    Hi All,

    We have a ASP 2.0 website that has been running since Dec/2009, without any issues on Windows 2003 R2, and Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
    (Build 3790: Service Pack 2).
    Starting last week, the site now errors out with the following error.

    Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    Source Error:
    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:
      System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
    System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
    System.Data.SqlClient.SqlDataReader.get_MetaData() +62
    System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
    System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
    System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
    System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
    System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
    System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
    System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
    System.Web.UI.WebControls.ListControl.PerformSelect() +31
    System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
    System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
    System.Web.UI.WebControls.BaseDataBoundControl.set_RequiresDataBinding(Boolean value) +1864940
    System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewChanged(Object sender, EventArgs e) +15
    System.Web.UI.DataSourceView.OnDataSourceViewChanged(EventArgs e) +114
    System.Web.UI.WebControls.SqlDataSourceView.SelectParametersChangedEventHandler(Object o, EventArgs e) +25
    System.Web.UI.WebControls.ParameterCollection.OnParametersChanged(EventArgs e) +22
    System.Web.UI.WebControls.Parameter.UpdateValue(HttpContext context, Control control) +137
    System.Web.UI.WebControls.ParameterCollection.UpdateValues(HttpContext context, Control control) +103
    System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control) +36
    System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList) +276
    System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +754
    System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
    System.Web.UI.WebControls.ListControl.PerformSelect() +31
    System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
    System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
    System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
    System.Web.UI.Control.PreRenderRecursiveInternal() +77
    System.Web.UI.Control.PreRenderRecursiveInternal() +161
    System.Web.UI.Control.PreRenderRecursiveInternal() +161
    System.Web.UI.Control.PreRenderRecursiveInternal() +161
    System.Web.UI.Control.PreRenderRecursiveInternal() +161
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

    Any idea what can be causing this issue?

    Thanks,

    Tuesday, October 9, 2018 8:12 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User921007156 posted

    Hi All,

    The issue appears to be resolved! The table that had over 145 million records, didn't have a good index on it. Once I created a decent index, the default page displayed without any issue, and also displayed in seconds!cool

    Everyone is happy, and shocked at how fast it runs now. I plan on working with the SQL DBA, to really give the site a good performance upgrade by creating good indexes.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 26, 2018 11:17 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    User2053451246 posted

    Maybe your database now has so much data that it is legitimately timing out?

    Tuesday, October 9, 2018 8:30 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User921007156 posted

    You maybe correct. I believe the main table it has to query before the default page is displayed has 165 million records.

    Tuesday, October 9, 2018 10:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User-943250815 posted

    I had same issue some years ago.
    And had to reconsider/refactor my queries before default page (a DashBoard).
    Suggest you, in mean time, set Timeout on Database Connection String and if using Linq DatabaseContext  set CommandTimeout property on initialization, just to get some more time while you review what can be done with query, indexes, or even in default page.

    Tuesday, October 9, 2018 11:35 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User61956409 posted

    Hi MattLong,

    You can try to debug or implement log in your application to find and trace which code causes this exception. 

    The exception can come because of query timeout, connection timeout etc. This blog explained the possible causes of this exception, you can refer to it.

    Besides, as you said, the application is running for many years, too many data might be stored in your database. If possible, you can try to archive&reomve old data from current table into the Archive table (or in other database), which should be able to improve the performance.

    With Regards,

    Fei Han

    Wednesday, October 10, 2018 5:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User921007156 posted

    At the moment I'm unable to remove the data from production. I finally was able to rebuild the old website in Visual Studio. I setup the debugger to stop at all the SQL calls on the client side, but when I step threw the code. It just goes threw the Page_Load and Page.IsPostBack methods, then the browser opens, and its just a white screen, then I get the error. I turned on my trace, and I get the following trace info below.

    Any idea how I can debug on the server side?

    Trace Information
    Category Message From First(s) From Last(s)
    aspx.page Begin PreInit
    aspx.page End PreInit 0.501799285185543 0.501799
    aspx.page Begin Init 0.501884366380149 0.000085
    aspx.page End Init 0.502179998702263 0.000296
    aspx.page Begin InitComplete 0.502192909856309 0.000013
    aspx.page End InitComplete 0.502201848347571 0.000009
    aspx.page Begin PreLoad 0.502210124728369 0.000008
    aspx.page End PreLoad 0.502230981207981 0.000021
    aspx.page Begin Load 0.502240250754475 0.000009
    aspx.page End Load 18.7325659693761 18.230326
    aspx.page Begin LoadComplete 18.7340206260652 0.001455
    aspx.page End LoadComplete 18.7469688582964 0.012948
    aspx.page Begin PreRender 18.7486072506393 0.001638
    Unhandled Execution Error
    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
      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.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
      at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
      at System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
      at System.Web.UI.WebControls.ListControl.PerformSelect()
      at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
      at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
      at System.Web.UI.WebControls.BaseDataBoundControl.set_RequiresDataBinding(Boolean value)
      at System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewChanged(Object sender, EventArgs e)
      at System.Web.UI.DataSourceView.OnDataSourceViewChanged(EventArgs e)
      at System.Web.UI.WebControls.SqlDataSourceView.SelectParametersChangedEventHandler(Object o, EventArgs e)
      at System.Web.UI.WebControls.ParameterCollection.OnParametersChanged(EventArgs e)
      at System.Web.UI.WebControls.Parameter.OnParameterChanged()
      at System.Web.UI.WebControls.Parameter.UpdateValue(HttpContext context, Control control)
      at System.Web.UI.WebControls.ParameterCollection.UpdateValues(HttpContext context, Control control)
      at System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control)
      at System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList)
      at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
      at System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
      at System.Web.UI.WebControls.ListControl.PerformSelect()
      at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
      at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
      at System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e)
      at System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e)
      at System.Web.UI.Control.PreRenderRecursiveInternal()
      at System.Web.UI.Control.PreRenderRecursiveInternal()
      at System.Web.UI.Control.PreRenderRecursiveInternal()
      at System.Web.UI.Control.PreRenderRecursiveInternal()
      at System.Web.UI.Control.PreRenderRecursiveInternal()
      at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)    		 49.4952735244537 30.746666
    Wednesday, October 17, 2018 9:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User61956409 posted

    Hi MattLong,

    If possible, you can execute same Query as you did in your application in your database and check&share us the actual Query execution time.

    With Regards,

    Fei Han

    Thursday, October 18, 2018 7:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User921007156 posted

    Hi All,

    I found out a few things, since I got the app setup in VS.

    1. My DBA says the following settings are on the database.
    Connection timeout is 30, query is 600

    2. I found 4 SQL queries on the default page, that were not in my C# code, but on the input drop-list control, within design level, on the design page, as follows...

    <asp:SqlDataSource ID="_shiftDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:conn %>"
    SelectCommand="SELECT shiftmetricname, id FROM shiftmetrics WHERE (id IN (SELECT shiftmetric_ID FROM Sample WHERE (Availablity_ID = @availablity AND Performance_ID = @performance AND Quality_ID = @quality))) order by shiftmetric" CancelSelectOnNullParameter="False">
    <SelectParameters>
    <asp:ControlParameter ControlID="_availablityDropDown" Name="availablity" PropertyName="SelectedValue" />
    <asp:ControlParameter ControlID="_performanceDropDown" Name="performance" PropertyName="SelectedValue" />
    <asp:ControlParameter ControlID="_qualityDropDown" Name="quality" PropertyName="SelectedValue" />
    </SelectParameters>
    </asp:SqlDataSource>

    3. One of the SQL statements takes 220 secs to run in my dev environment, and takes 30-65+ secs in production. Its the longest running query of the four.


    Since the SQL statements are ran at the server, within the ASPX part of the code on the server side. How can I debug this part of the code?
    What is the default timeout in the SelectCommand on the ASPX default page, if my database is set to 600 sec on query?
    Is there a way for me to increase the timeout without any code change here?

    Thanks,

    Tuesday, October 23, 2018 4:45 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User753101303 posted

    Hi,

    How many rows does it return. Yoou could also use https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-and-save-execution-plans?view=sql-server-2017 to see how it runs and maybe fix db side issues.

    Not sure if the db side value is used. It might be overriden client side maybe and anyway if it doesn't seems the request should really take that long it is likely better to first understand why it is slow and to increase the timeout as a last resort.

    Tuesday, October 23, 2018 12:34 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User921007156 posted

    The query returns about 15 rows.
    How do  I debug the server side?
    Any suggestions on how to fix this?

    Thanks,

    Wednesday, October 24, 2018 6:27 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User753101303 posted

    in particular https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql?view=sql-server-2017  to see how the query peforms.

    As you get back only 15 rows it seems that for now the issue is that SQL Server can'r efficiently retrieve the rows you want from your millions of rows (possible missing indexes ?)

    I would run the query in SQL Server Management studio to see how it runs.

    Wednesday, October 24, 2018 7:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    User921007156 posted

    Hi All,

    The issue appears to be resolved! The table that had over 145 million records, didn't have a good index on it. Once I created a decent index, the default page displayed without any issue, and also displayed in seconds!cool

    Everyone is happy, and shocked at how fast it runs now. I plan on working with the SQL DBA, to really give the site a good performance upgrade by creating good indexes.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 26, 2018 11:17 PM