Avatar of Richard Mancuso
Richard Mancuso
Flag for United States of America asked on

How to resolve "Server Error in '/' Application"

I have an ASP.Net application with a SQL Server backend.  Occasionally one of my users will get an error "Server Error in '/' Application"
"The Wait Operation Timed Out". I can't figure out what is happening.  The underlying query runs in 1 second in SQL Management Studio.  I don't recall getting this error on my browser.
The Webserver and SQL Server are on the same machine.  Is this error at the machine level or does it have to do with the User's internet connection?
I need to make this error go away!
I've attached a screenshot of the error.
ApplicationError.png
ASP.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
Richard Mancuso

8/22/2022 - Mon
Miguel Oz

When you say 1 second. Do you use the same query parameters that your user has used prior the error.
This type of timeout can have three causes;
1.Your user may have a very slow connection that it takes more than 30 seconds (typical timeout), you can try adjusting the SQL connection timeout and well as your IIS website timeout.
2. There's a deadlock somewhere
3. The database's statistics and/or query plan cache are incorrect
I will try adjusting timeouts first, for the others I will use activity monitor of your SSMS to check when your particular user is connected to your system.

If more help needed, please provide version details of:
- SQL server.
- .NET framework
- ASP.NET
- Server and IIS
- Grid component version
Also send your code  setting up the data source.
Richard Mancuso

ASKER
Hi Miguel.

Thanks for your response.  I really appreciate it.  
The stored procedure in question runs reliably in 1 second in SQL Mgmt Studio.  I set the procedures to always recompile and to for IIS to use the
First the procedure is very efficient and no matter the parameters, it takes 1 second to run in SQL Mgmt Studio.  I set ARITHABORT to ON in the stored procedure to that .NET will be forced to use that option.

How can I adjust the SQL connection and IIS website timeouts?  (I never know the right place to do this.)

How do you know that this error is between the User's computer and the website and note between the website and the database?
How can I find out if there's a deadlock?  My queries select on tables with (NOLOCK).
The website uses a generic SQL User to call on the database.  I can't isolate a person's activity.

SQL Server 13
.Net Framework 4
ASP.Net 4.71
Windows Server 2016, IIS 10
Grid Version - Infragistics 17.1

Maybe the Data Source connection is old or at fault?  It's not clear to me how to tell the version I'm using or if it's up to date.  

<add name="ConnectionString" connectionString="Data Source=00.00.000.00;Database=MyDatabase;User ID=MyUser;Password=MyPassword" providerName="System.Data.SqlClient"

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="GetWcWeekliesByBranch" SelectCommandType="StoredProcedure"
    CancelSelectOnNullParameter="False" >
    <SelectParameters>

        <asp:ControlParameter ControlID="WeekEnding" Name="WeekEnding" PropertyName="Value" Type="DateTime" />
        <asp:ControlParameter ControlID="Metric" Name="Metric" PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="CustomerId" Name="CustomerId" PropertyName="SelectedValue" Type="Int32" />
        <asp:Parameter Name="DepartmentId" Type="Int32" />
        <asp:ControlParameter ControlID="RecruitedById" Name="RecruitedById" PropertyName="SelectedValue" Type="Int32" />
        <asp:ControlParameter ControlID="PlacedById" Name="PlacedById" PropertyName="SelectedValue" Type="Int32" />

    </SelectParameters>
Miguel Oz

If all the other users do not have any issues and it is just a particular user then it gives you some idea that it could be the problem.
How many rows are you returning?
Grid could be very slow for thousands of rows. You SQl execution may not be the issue but the rendering.
Gvien the fact you have only one SQl user you should add some log lines on your page in question.with a time stamp so that you can see how long does it take to execute your page.

See:
https://stackify.com/beyond-iis-logs-find-failed-iis-asp-net-requests/
https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.eventlog.writeentry?view=netframework-4.8

My other suggestion is always use code to setup components not markup, you have more control and it  could execute faster than  your third party code. Furthermore if your query is fast then you do not need to do any timeout.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Richard Mancuso

ASKER
HI Miguel,

I'm returning under 100 rows.  This error is usually happening on pages with heavy calculations.  As I mentioned earlier,I was thinking that maybe .Net was messing with the query plan so I set arithabort to ON.  

I will investigate logging.

I don't follow your last suggestion.  Are you saying to get rid of the Infragistics WebDataGrid or the SQLDataSource control?
Miguel Oz

Basically you are loading the grid using markup , like show here:

https://www.c-sharpcorner.com/article/data-binding-and-crud-operations-in-asp-net-webdatagrid/

but you can use for example a dataset to load your info: (More work of course but the SQL connection is not dependent of the grid component)
https://help.infragistics.com/Help/Doc/ASPNET/2011.1/CLR4.0/html/WebGrid_Updating_Data_with_a_Data_Set.html

I do not think it is .NET messing up is your grid control taking so long to fetch and prodcuing the grid, we can confirm that once you have log results.
arnold

Check your error logs.
What web app do you have / replaces index.aspx etc.

You can setup an error handler to notify you of an event that would provide you with details about the request and where the issue occurred.

From the error little can be said.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Richard Mancuso

ASKER
Thanks for all of your comments.  I looked at the logs and the error messages.  The problem here is that the Stored Procedure was timing out.,  This could be due to parameter sniffing.  There are 2 remedies.  I can improved performance of the stored procedure through Indexes or a Rewrite.  I can extend the Command Timeout property of the SQL Data Source.  

This snippet would remove any timeout.
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
            e.Command.CommandTimeout = 0;
        }
ASKER CERTIFIED SOLUTION
Richard Mancuso

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question