Sql database randomly gives abnormal responses to web requests (ASP.NET/VB)

Question

1.00/5 (1 vote)

See more:

I am developing an asp.net website which uses a System.Data.SQLClient connection to transfer data between the database and the web application. At first glance everything seems to be working properly as all data is displayed as it should be,and all functions do the work they're meant to do. However,at random time intervals - about 24 hours,usually - ,a freak error occurs: The responses given to the application from the database seem to be abnormal,like giving a "User not found" message to all users of the app when they try to login (they work properly before the "hang"!) or a "table not found" error message for a table that is there and was working fine 5 minutes earlier.
In order to get the application working,I must refresh the website,either via IIS - I guess this refreshes the AppPool - or reupload the application's .dll files to the server,without changing anything.
Any idea on what is causing the issue? I have never experienced it before and it's been frustrating me for quite some time now.

I use VB.Net/WebForms and .net framework 4.5.2

What I have tried:

- Changing the session timeout values
- Changing my "SELECT..." queries to not include "*"
- Contacting my hosting provider - they were unable to help -
- Checking if the connection exists and/or hangs - it exists/doesn't hang -

Posted 17-Apr-17 20:07pm

Fydobas

Updated 17-Apr-17 22:34pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

NightWizzard · Accepted Answer · 2017-04-17T22:34:00

Solution 1

I can't give an exact pointer to the error source but from a +30 years experience with MS SQL and +10 years experience with .NET I can say that this is no typical behaviour wether for SQL Server nor the SQL client libraries of .NET. I would search for environmental reasons like:
- is there any memory leak leading to an out of memory error at the server
- has the SQL server instance enough memory to work properly
- does the server a backup up task at the time of the error
- does the server a periodically reconnect due to scheduled maintainance actions
- exists any SQL agent job that performs long time operations at that moment causing
a timeout or a deadlock situation for other tasks
- is your server dedicated to your ASP website only or shared with other websites
that may cause heavy server action
- does the provider perform any kind of periodically re-connect or reset for your server

Hope these ideas help to find the reason for that strange behaviourd - goo luck!

Posted 17-Apr-17 22:34pm

NightWizzard

Comments

Fydobas 18-Apr-17 6:46am

Thank you very much,I will try to see if any of these help and will inform you afterwards

Fydobas 24-Apr-17 5:09am

So I found the issue... It has to do with SQL Server running two commands simultaenously (e.g. one user logging in and another logging out,beware that we are talking about the exact same millisecond).
Logging in and out is achieved by two SQL procedures,each of whom has its own transaction on,which (the transaction) has COMMIT and ROLL BACK statements. (No,I do not use Forms Authentication)
Is there a specific solution for this issue? Because it has never happened to me before...

NightWizzard 24-Apr-17 13:10pm

OK, so it's seems to be clear that there is a deadlock situation if 2 users login at the same time because of the SQL transactions that are started then. As you can read at Microsoft's website, there is no way to avoid deadlocks completely but you can reduce the risk for that by some tipps you can find here:
https://subhrosaha.wordpress.com/2010/06/02/sql-server-avoiding-deadlocks/
or here:
http://stackoverflow.com/questions/40585374/preventing-deadlocks-in-sql-server

There are a lot more matches if you google for
t-sql avoid prevent deadlock
Hope you'll find a solution for your problem - good luck again!

Fydobas 26-Apr-17 1:31am

So,problem solved (or it seems so after two days of extensive testing):

I was connecting once - that was,when a user opened the website - and then used the same connection for everything. So,SQL server treated every command as if it was being sent from one sole connection; which,naturally,caused a load of deadlocks. Issue was solved by closing and re-opening the connection each time I sent a command to SQL server: seems like it needs to be this way!

NightWizzard 26-Apr-17 13:32pm

I'm glad you've found the answer to solve this problem. Yes, depending on the way the database layer is designed, it may cause those deadlocks. Maybe you should change from SQL Client library to Entity Framework Code First? It's simple to integrate and handles connections automatically. With option
MultipleActiveResultSets=True
within the connection string you may have simultaneous access for multiple concurrent users.