connection pooling problem w/ ASP.NET 2.0 and SQL 2005 64 bit?

Here's the gist of the problem I am having:
I just deployed my new ASP.NET 2.0 / C# web app to "production". I have a
web server running Windows 2003, all the latest updates, etc. As mentioned,
the web app is running under ASP.NET 2 and C3. On a separate server, I am
running SQL Server 2005 64 bit, Standard Edition (SP1).
Note, I have separate development and test environments that mimic the above
setup. That is I have a dev web server accessing a separate dev database
server; the only difference is that the db server is SQL 2005 32 bit, Std
Edition; same in test.
My production web app is experiencing connection pooling problems. If I
bring up the activity monitor on the production db server while I am
exercising some of the web pages, I can watch the connections grow to the
100 max (the default for asp.net) very quickly (just one person hitting a
page that exercises a database query) and then the .NET app will eventually
throw an error:
Message: Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool. This may have occurred because all pooled
connections were in use and max pool size was reached.
Source: System.Data...
Some things to consider:
1) Before everyone jumps to the conclusion about datareaders, please bear in
mind that the EXACT same .NET code is on dev and test web servers; I am NOT
able to get the connections to max out on dev or test; whereas on production
web server, I can easily get it to max out. It's not a code issue. Yes, I
know all about closing DataReader connections explicitly, blah, blah. It's
not the code.
2) There are 2 major difference on the db server; however
a) the production server is 64 bit; my other db servers are 32 bit
b) I actually spent quite a bit of time today on my production db server in
an effort to get linked server stuff working. So, there are some definite
things I did on the prod server db that I have *not* done on dev or test sql
boxes, such as
- turning on MS DTC security config settings (in Component Services); there
are many MS blogs about this and the following MSKB:
http://support.microsoft.com/kb/899191
- enabling named pipes
So, I'm thinking I have 2 straws to grasp at:
A) call Microsoft tech support to see if there's anything that google hasn't
turned up
B) uninstall SQL Server 2005 on my production db server and reinstall using
a 32 bit version. Of course, since this is production. I bought 64 bit SQL,
so would I be "allowed" to downgrade to 32 bit? The only additional disc
media I have are MSDN discs.Hi,
Thanks for using Microsoft Managed Newsgroup.
From your description, I understand that:
Your ASP.NET 2.0 web application with SQL Server 2005 64 bit SP1 was
running on a production environment. The database server was on a separated
machine. You found that the connections grew quickly to reach the max
connection limit on your SQL Server and then any new connecion was timeout.
However no problem appeared on your test and development environment.
If I have misunderstood, please let me know.
For further research, I would like your answering me several questions:
1. How long had the SQL Server been normally running before the problem
appeared?
2. What are the situations of CPU and memory usage at that time?
3. Could you see some errors in the Event logs?
Also, I would like to collect the following information from you:
1. MPSReport:
Please help create a MPSReport on the problematic machine, which will
collect
ERRORLOG, Windows event log and other helpful information about the SQL
Server. To
create a MPSReport, please visit the following web site:
<http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F
-88B7-F9
C79B7306C0&displaylang=en>.
And look for MPSRPT_SQL.EXE. Download it and run it on the machine. Dismiss
the
prompted dialog boxes and the readme window (usually a Notepad) after you
read
them. After the Command Prompt window closes itself, collect the cab file
the tool
has generated and sent it to me.
2. SQL Error log:
By default, they are located at: C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG -- Send all the files under this folder with zip
format to me.
3. Server Configurations:
Use sqlcmd or osql to run the following batch and send the output file to
me:
Under command prompt: sqlcmd -S ServerName\InstanceName -E -o C:\output.txt
exec sp_configure 'show advanced', 1
reconfigure with override
exec sp_configure
go
4. SQL Trace file
Use SQL Profiler to monitor your SQL Server to see what are the SQLs or SPs
being executed at that time.
I will create a web file space for your uploading the large files. However
you may need to leave me your email so that I can mail you the password.
If it is not convenient for you to leave your email here, you can send an
email to me (changliw@.microsoft.com).
Note: Our managed newsgroup is focused on break/fix issues that are neither
urgent nor complex. If the issue is urgent to your business, it is
recommended that you contact Microsoft Customer Support Services (CSS) via
telephone so that a dedicated Support Professional can assist you in a more
efficient manner. Please be advised that contacting phone support will be a
charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
Just check with you to see if you need further assistance on this issue.
We appreciate your posting back at your convenience and let us know the
issue status.
If you have any other questions or concerns, please feel free to let us
know. It is always our pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support

Connection manager permission issue

Hi there,

I apologize upfront if this is an old issue but i couldn't find the solution after searching quite a bit.

I have a OLE db connection manager and an ADO.net db connection manager and both of them fail when i run them in VS dev environment:

Connection may not be configured correctly or you may not have the right permissions on this connection

When I set up my connection managers, they do connect fine but no matter which one i try to use with an Execute sql task, the above error keeps popping up.

The connection managers are both set up to use SQL Authentication and the database is not on my local machine but instead on a server and my package security is set to 'DontSaveSensitive'. I have not yet deployed so this is all in my VS.net dev environment where it's failing.

I have also turned off the firewall on both the development server and my pc. All I want to be able to do is let the connection managers point the the dev server as the db is on there.

Any suggestions?

Regards
Mike
Hi,

I found a solution for the problem and thought it might be useful to post it for those that seek to develop using a remote dev server that contains the database.

I declared a variable that contained the connectionstring for the specific connection manager (note that the ado.Net connection string will look slightly different than oledb for instance) and in the connection manager's expression property pointed it to the variable.

I got pointed into this direction by reading the article 'Suggested best practices' by Jamie Thomson :
http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx

Thanks for that excellent article Jamie
Regards
Michael

Connection failures

Hi all,
I need a bit of help with a really strange problem we've got with out
SQL cluster. The setup is - 2 windows 2003 nodes, sharing a das, sql
2000 enterprise sp4. Cluster was up and running for a couple of days,
the one of our offices said they could not connect to it. This box is
held in one office, we have 5 or so other offices that connect to this
cluster, and only one of them could not connect.
So, network problems? Well remote desktop to either node of the cluster
and we can connect to the machines in the remote office. Setup ethereal
on a PC in the remote office, try to connect via query anaylzer to the
cluter and we can see packets going back and forth happily with the IP
of the cluster etc. and no other traffic on the line is affected. Q
hair being pulled.
So, we failover it over to the second node. Everything starts working
again. Please explain that one. We give up for the afternoon to save
our hair.
Skip to a few days later (today now). And it all fails again, same as
last time. Failback to first node, it still doesn't work, fail over to
second, oh it work again. For a few minutes, then dies again.
Heeeeeelllp!!!
Treat this as you would any network communications failure.
Name resolution
IP path resolution (ping)
SQL client connect (Query Analyzer)
application connect.
Somewhere you will find where the glitch is. I would guess DNS cache
problems on the client, but that is just to impress everyone if it really is
the problem.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<kristan.mcdonald@.googlemail.com> wrote in message
news:1165936405.727921.56250@.16g2000cwy.googlegrou ps.com...
> Hi all,
> I need a bit of help with a really strange problem we've got with out
> SQL cluster. The setup is - 2 windows 2003 nodes, sharing a das, sql
> 2000 enterprise sp4. Cluster was up and running for a couple of days,
> the one of our offices said they could not connect to it. This box is
> held in one office, we have 5 or so other offices that connect to this
> cluster, and only one of them could not connect.
> So, network problems? Well remote desktop to either node of the cluster
> and we can connect to the machines in the remote office. Setup ethereal
> on a PC in the remote office, try to connect via query anaylzer to the
> cluter and we can see packets going back and forth happily with the IP
> of the cluster etc. and no other traffic on the line is affected. Q
> hair being pulled.
> So, we failover it over to the second node. Everything starts working
> again. Please explain that one. We give up for the afternoon to save
> our hair.
> Skip to a few days later (today now). And it all fails again, same as
> last time. Failback to first node, it still doesn't work, fail over to
> second, oh it work again. For a few minutes, then dies again.
> Heeeeeelllp!!!
>
|||Hi Geoff,
Thanks for the advice, the weird thing about this is other network
connections seem fine, it's just SQL that fails, and even then it's not
a total block. Name resolution seems to work fine and I can connect to
the shares on the server and copy files off so there's nothing wrong
with the physical layer. If I try to connect using query analyser, it
will reject my password and I'll get a login failed if I enter the
wrong one, or try to open the connection if I use the correct one, so
at least at some level SQL is responding to these requests.
We had an incident yesterday where the remote office lost SQL again,
however when odbcping and isql, I seemed to be able to connect and run
queries. I wouldn't treat this as gospel though as everything was a bit
rushed and confused trying to get connectivity restored. It also seemed
that if you turned off the object browser in query analyzer you could
get a connection.
I'm starting to think it might be SQL server itself, but I don't know
enough about how query analyzer connects to verify it, and it still
doesn't explain why the odbc application connections fail... or does
it?
Any help appreciated!
Thanks,
Kristan
Geoff N. Hiten wrote:

> Treat this as you would any network communications failure.
> Name resolution
> IP path resolution (ping)
> SQL client connect (Query Analyzer)
> application connect.
> Somewhere you will find where the glitch is. I would guess DNS cache
> problems on the client, but that is just to impress everyone if it really is
> the problem.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
|||What do your SQL error logs say?
What is your configuration? Are you running per Processor Licenses or
CAL-based?
Do you have any concurrent connection limits configured? What does
sp_configure show?
You said the system was SP4. Have you applied the 2187 hotfix yet?
I think you need to start fresh. You say the connections are intermittent
and only for one branch. Are the lost connections always from this one
branch? If so, it is your physical layer, in the network. Check your
routers from dropped packets. Make sure no one is using AUTO NEGOTIATE on
the network link and duplex.
Is there anything special about this branch? Is it the furthest away? Does
it have the slowest connection speed? Does it have the most users?
If an outage is sporadic but affects everyone equally, it is the system
resource, or at least a common point in between. If you can localize a
problem (like it is affecting only one branch or one group of users), then
it will be somewhere unique to them, but at a common point for them, like a
router at the branch site.
Hope this helps, but without any additional data, that's about the best
we've got.
Sincerely,
Anthony Thomas

<kristan.mcdonald@.googlemail.com> wrote in message
news:1166013373.732672.101210@.j44g2000cwa.googlegr oups.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the advice, the weird thing about this is other network
> connections seem fine, it's just SQL that fails, and even then it's not
> a total block. Name resolution seems to work fine and I can connect to
> the shares on the server and copy files off so there's nothing wrong
> with the physical layer. If I try to connect using query analyser, it
> will reject my password and I'll get a login failed if I enter the
> wrong one, or try to open the connection if I use the correct one, so
> at least at some level SQL is responding to these requests.
> We had an incident yesterday where the remote office lost SQL again,
> however when odbcping and isql, I seemed to be able to connect and run
> queries. I wouldn't treat this as gospel though as everything was a bit
> rushed and confused trying to get connectivity restored. It also seemed
> that if you turned off the object browser in query analyzer you could
> get a connection.
> I'm starting to think it might be SQL server itself, but I don't know
> enough about how query analyzer connects to verify it, and it still
> doesn't explain why the odbc application connections fail... or does
> it?
> Any help appreciated!
> Thanks,
> Kristan
> Geoff N. Hiten wrote:
really is
>
|||Thanks for your help and suggestions - SQL error logs don't indicate
anything, and we're running per-processor. No connection limits set,
and I've not applied 2187 but will shortly.
We've got a bit deeper with this now, and it seems like it may be line
related as we've found a few more apps (exchange for one) that seems to
suffer a similar problem.
Connecting via ISQL when the outage is taking place works, however
doing something like select * from sysdatabases (which I guess is
similar to what query analyzer does at login if the object browser is
showing) I get only the first 10-15 databases (same place each time)
and it dies. Looking at packet traces from both ends there's loads of
retransmission and out of sequence arrivals going on, so I'm now 99%
certain it's not SQL related.
Still doesn't explain why I can run the same query on a box on exactly
the switch and it will work, whereas doing in on the cluster will fail.
We're working with the network providers at the moment, but they're
trying to claim if it works on one box then it must be cluster related.
Thanks guys,
Kristan
Anthony Thomas wrote:
[vbcol=seagreen]
> What do your SQL error logs say?
> What is your configuration? Are you running per Processor Licenses or
> CAL-based?
> Do you have any concurrent connection limits configured? What does
> sp_configure show?
> You said the system was SP4. Have you applied the 2187 hotfix yet?
> I think you need to start fresh. You say the connections are intermittent
> and only for one branch. Are the lost connections always from this one
> branch? If so, it is your physical layer, in the network. Check your
> routers from dropped packets. Make sure no one is using AUTO NEGOTIATE on
> the network link and duplex.
> Is there anything special about this branch? Is it the furthest away? Does
> it have the slowest connection speed? Does it have the most users?
> If an outage is sporadic but affects everyone equally, it is the system
> resource, or at least a common point in between. If you can localize a
> problem (like it is affecting only one branch or one group of users), then
> it will be somewhere unique to them, but at a common point for them, like a
> router at the branch site.
> Hope this helps, but without any additional data, that's about the best
> we've got.
> Sincerely,
>
> Anthony Thomas
>
> --
> <kristan.mcdonald@.googlemail.com> wrote in message
> news:1166013373.732672.101210@.j44g2000cwa.googlegr oups.com...
> really is
|||Then definitely check your AUTO NEGOTIATE settings on both the switch ports
and the NICs for all players, clients and server, for both link speed and
duplex. This would explain why sometimes you have a problem and sometimes
you don't, for some clients and not others.
Usually these can reset after patching the bios on the switches.
Sincerely,
Anthony Thomas

<kristan.mcdonald@.googlemail.com> wrote in message
news:1166619576.945335.285910@.f1g2000cwa.googlegro ups.com...[vbcol=seagreen]
> Thanks for your help and suggestions - SQL error logs don't indicate
> anything, and we're running per-processor. No connection limits set,
> and I've not applied 2187 but will shortly.
> We've got a bit deeper with this now, and it seems like it may be line
> related as we've found a few more apps (exchange for one) that seems to
> suffer a similar problem.
> Connecting via ISQL when the outage is taking place works, however
> doing something like select * from sysdatabases (which I guess is
> similar to what query analyzer does at login if the object browser is
> showing) I get only the first 10-15 databases (same place each time)
> and it dies. Looking at packet traces from both ends there's loads of
> retransmission and out of sequence arrivals going on, so I'm now 99%
> certain it's not SQL related.
> Still doesn't explain why I can run the same query on a box on exactly
> the switch and it will work, whereas doing in on the cluster will fail.
> We're working with the network providers at the moment, but they're
> trying to claim if it works on one box then it must be cluster related.
> Thanks guys,
> Kristan
> Anthony Thomas wrote:
intermittent[vbcol=seagreen]
on[vbcol=seagreen]
Does[vbcol=seagreen]
then[vbcol=seagreen]
like a[vbcol=seagreen]
not[vbcol=seagreen]
bit[vbcol=seagreen]
seemed[vbcol=seagreen]
cache
>