August 16, 2011 at 4:50 pm
In the config file of a .net application I have this key used to store a connection string:
<add key="ConnectionString" value="server=*********;Trusted_Connection=YES;database=**********;Integrated Security=true;Connect Timeout=0"/>
I'm under the impression that 'Connect Timeout=0' means 'take as long as you need to connect'.
If I publish that web site to a Windows 2008 R2 (web) server running IIS 7.5 which connects to a separate server running SQL Server 2008 - the web site stops responding whenever more than 2 people are browsing it.
If I open the web site in a browser, move to the desk next to mine and open it on someone else's PC - it will work fine. If I move to a third PC and try to open the web site in a browser - the browser just hangs - nothing happens. There are no error messages and nothing is raised in the server event logs.
I know absolutely by a process of elimination that it is the connection to the database that is causing the problem. If I remove all code on my home page that retrieves data from the database - the site works perfectly with any number of people able to browse it.
If I change it to 'Connect Timeout=15' it seems to fix the problem (so far, at least). So, my question is .... does 'Connect Timeout=0' mean 'take as long as you like to connect to the database' or not? What else can it mean - you surely can't expect a connection to be instantaneous?
August 17, 2011 at 12:23 am
As per my understanding, 0 is for the default configuration as set up in database server.
Related to number of user not able to login, it seems like application issue not the database issue. If you have sql client tool on your user machines then you can connect sql directly instead of connecting from application.
And I am sure when you will connect database from sql client tool, you will be able to connect from more than 2 different user session.
----------
Ashish
August 17, 2011 at 1:55 am
One should avoid timeout values 0 (wait forever) as much as possible !
Take control of your system a determine an appropriate timeout value for your app to connect to your db !
If the timeout has been set to 15, does it connect and execute your queries or does it generate errors ?
Regarding more than 2 users:
- what type of transaction isolation lever does your app use for its queries ?
- does your app use transactions ?
- does your app use locking hints ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 17, 2011 at 3:18 am
ALZDBA (8/17/2011)
If the timeout has been set to 15, does it connect and execute your queries or does it generate errors ?
Yes, it seems to be working perfectly with the timeout set to 15
ALZDBA (8/17/2011)
Regarding more than 2 users:
- what type of transaction isolation lever does your app use for its queries ?
I've never set the transaction isolation level ... I never use transactions if I am just doing a Select. If I am doing an update, delete, or insert I just use ..
BEGIN TRANSACTION
... do things ...
...... ROLLBACK TRANSACTION ... if something goes wrong
COMMIT TRANSACTION
ALZDBA (8/17/2011)
Regarding more than 2 users:
- does your app use transactions ?
Yes - any update, insert or delete statement that is run on related tables is run in a transaction. For the purpose of testing to try to find out what was going wrong - all I was doing was calling a stored procedure that selected data from one table. It was returned to the calling app and used to populate a SqlDataReader which, in turn, was used to populate a drop down list.
ALZDBA (8/17/2011)
Regarding more than 2 users:
- does your app use locking hints ?
No locking hints are used. The database in question is used by 6 separate applications. They all run on a Windows 2003 server running IIS 6 - and were developed using VS2005 and use .net Framework 2.0
The big difference with this (the 7th) application is that it has been developed in VS2010 and uses .net Framework 4.0 and is hosted on a Windows 2008 R2 server running IIS 7.5
The code in the 7th application - in terms of the Data Access class - all the log in stuff etc. - is all identical to the code in the other 6 apps. The main difference is that each app accesses its own tables and has its own stored procedures. Otherwise, everything is the same.
Thanks for your reply.
August 17, 2011 at 6:19 am
Just one question, by any chance is it running under debug mode? That is the only thing that comes to mind.
-Roy
August 17, 2011 at 8:08 am
Roy Ernest (8/17/2011)
Just one question, by any chance is it running under debug mode? That is the only thing that comes to mind.
No, at least I say 'no' but it's a fast paced and pretty lax development environment - in that whilst the systems are being developed for each team, mods to the app are published to the live server quite frequently. And, yes, there may be times where I forget to change ...
<compilation debug="false" targetFramework="4.0">
... and it gets published to live with debug="true". But, not in this case.
As I say, I regularly publish live and forget to change debug to false - I've never noticed any issues before. You find this affects things to a noticeable extent?
August 18, 2011 at 11:07 am
In case it helps anyone else ...
August 19, 2011 at 1:17 am
Thank you for the feedback. :smooooth:
I've been looking for such kind of bug info for your thread, but apparently did google/bing the wrong key words :crazy:
It seems to be spot on.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 13, 2016 at 11:11 am
I have the same issue.
Able to reproduce in SSMS by changing the connection timeout = 0. SSMS just sits there forever.
We are able to say it is DB Server related as it is only happening to a couple of our servers but not to others. We are not able to change anything on those servers as the admin are not on our team.:) I have been looking into the pooled connections documentation to see if we could find anything. But as we only have 1/2 day to find an issue solution. We had to change the connection timeout to 180.:(. Cowering in corner forever.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply