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 pool leak in production machine (in other machine it runs well)

Hi to everyone,

I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.-

Thanks to everyone for any help.

How do you access DB? If you use some IDataReader, though they will be GC sooner or later, still remember to close them as soon as they are not used~|||

My big mistake is to misunderstood the meaning of the numberOfReclaimedConnection counter, that indicates the number of connections that are reclaimed by a garbage collector and indicate a leak. For some reason in my developement machine garbage collector does a more aggressive job in reclaiming connection while in the production server, maybe due to the large amount of ram garbage collection happens less frequently.

You're right about the datareder, one of the function in Data Access Layer forget to close a datareader in some situation, and it's called from a webModule that runs at every request for the page. The result is awful, when a lot of user access to the site the pool often become empty. I enclose the datareader in using block and all goes well again. From now on I will use perfmon.exe at every run of the site.

Alk.

Connection is busy with results for another hstmt

HI guys,

I'm using ODBC driver from WinXP SP2 with SQL Server 2005 Express (on my machine for test) and SQL Server 2005 Standard (on the production machine).

With SQL Server 2005 Express I've no problem, but with SQL Server 2005 Standard, in some query, I get "Connection is busy with results for another hstmt".

I'm working with VisualStudio 2003 with standard CDatabase and CRecordSet MFC classes.

I looked for an advice in the Microsoft KB but I found only Service Pack for SQL Server 2000 !

Everything could be useful !

Thanks a lot!

Bye, Domenico

You can avoid this error message by switching to the SQL Server 2005 ODBC driver, 'SQL Native Client' and enabling the MARS (Multiple Active Result Sets feature). There is more information in Books Online and also here http://blogs.msdn.com/dataaccess/archive/2005/08/02/446894.aspx

Connection

Hi
when i design a report using SQL 2005 and try to run it in production mode
with setting the servertarget rul property of the project to http://localhost
i get an error that it cant connect to the local host, but when i click the
preview tab i find the report with correct data so whats the problem ?
the OS is win 2003 enterprise
Ammar S. Mitoori
IT Head QIMCO Co.
Tel : +9744831199
Mobile : +9745378400
Fax : +9744831643
The url needs to be the path to the Reporting Services web service i.e.
http://localhost/ReportServer
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Pure Heart" <PureHeart@.discussions.microsoft.com> wrote in message
news:055ADB25-181E-41FE-BC2B-A081538FB646@.microsoft.com...
> Hi
> when i design a report using SQL 2005 and try to run it in production mode
> with setting the servertarget rul property of the project to
> http://localhost
> i get an error that it cant connect to the local host, but when i click
> the
> preview tab i find the report with correct data so whats the problem ?
> the OS is win 2003 enterprise
> --
> Ammar S. Mitoori
> IT Head QIMCO Co.
> Tel : +9744831199
> Mobile : +9745378400
> Fax : +9744831643
|||hi
thanx for ur replay i tried and it didnt work also,, so any suggestions ?
Ammar S. Mitoori
IT Head QIMCO Co.
Tel : +9744831199
Mobile : +9745378400
Fax : +9744831643
"Jasper Smith" wrote:

> The url needs to be the path to the Reporting Services web service i.e.
> http://localhost/ReportServer
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Pure Heart" <PureHeart@.discussions.microsoft.com> wrote in message
> news:055ADB25-181E-41FE-BC2B-A081538FB646@.microsoft.com...
>
>

Connectio to Remote SQL Server from another SQL Server Stored Proc

Hi,
I have a problem with a conection to Remote SQL Server Database wich have a
blank space in databese name.
Ex.
"Production ED"
I Can`t change the name of this data base.
I Need to connect this database with a Stored Procedure in another Server.
Both Databases are SQL Server 2000 Ent. Ed.
Would it help if you put the name in brackets?
[Production ED]
so you could do something like...
select * from
[Production ED].dbo.ProcedureName
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Esteban" <Esteban@.discussions.microsoft.com> wrote in message
news:7CA8D942-0B44-434C-BD4D-34E4231DC7E8@.microsoft.com...
> Hi,
> I have a problem with a conection to Remote SQL Server Database wich have
> a
> blank space in databese name.
> Ex.
> "Production ED"
> I Can`t change the name of this data base.
> I Need to connect this database with a Stored Procedure in another Server.
> Both Databases are SQL Server 2000 Ent. Ed.

Connectio to Remote SQL Server from another SQL Server Stored Proc

Hi,
I have a problem with a conection to Remote SQL Server Database wich have a
blank space in databese name.
Ex.
"Production ED"
I Can`t change the name of this data base.
I Need to connect this database with a Stored Procedure in another Server.
Both Databases are SQL Server 2000 Ent. Ed.Would it help if you put the name in brackets?
[Production ED]
so you could do something like...
select * from
[Production ED].dbo.ProcedureName
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Esteban" <Esteban@.discussions.microsoft.com> wrote in message
news:7CA8D942-0B44-434C-BD4D-34E4231DC7E8@.microsoft.com...
> Hi,
> I have a problem with a conection to Remote SQL Server Database wich have
> a
> blank space in databese name.
> Ex.
> "Production ED"
> I Can`t change the name of this data base.
> I Need to connect this database with a Stored Procedure in another Server.
> Both Databases are SQL Server 2000 Ent. Ed.

Connecting With Visual Studio .net

Hello,
I have MSDE Installed on a different computer (WIndows 2003) within my
network. I also have MSDE Installed on my production computer for
development.
I have successfully created databases on both computers using OSQL. What I
would really like to accomplish is a connection from the Server Explorer in
Visual Studio .NET.
I have a successful connection to the local instance of MSDE and I added my
server to the server list with no problems.
But, this is where the problem comes in.
Lets Say my Server is called Moon, The Servers Tree in Server Explorer
shows Moon, I can access the Event Logs, Message Queues, Performance
Counters and Services with on problem. But, when I click on SQL Servers
Moon is shown with a Red X, I click the Plus (+) sign, type in the Login and
Password, I receive the following Error:
{DBNETLIB}{ConnectionOpen (Connect().]SQL Server does not exist or access
denied.
Any Suggestions will be greatly appreciated,
Chuck
hi Chuck,
Charles A. Lackman wrote:
> Hello,
> I have MSDE Installed on a different computer (WIndows 2003) within my
> network. I also have MSDE Installed on my production computer for
> development.
> I have successfully created databases on both computers using OSQL.
> What I would really like to accomplish is a connection from the
> Server Explorer in Visual Studio .NET.
> I have a successful connection to the local instance of MSDE and I
> added my server to the server list with no problems.
> But, this is where the problem comes in.
> Lets Say my Server is called Moon, The Servers Tree in Server
> Explorer shows Moon, I can access the Event Logs, Message Queues,
> Performance Counters and Services with on problem. But, when I click
> on SQL Servers Moon is shown with a Red X, I click the Plus (+) sign,
> type in the Login and Password, I receive the following Error:
> {DBNETLIB}{ConnectionOpen (Connect().]SQL Server does not exist or
> access denied.
>
the most probable causes of this problem are listed in
http://support.microsoft.com/default...06&Product=sql
... please check with the Server Network Utility that the Moon MSDE instance
has enabled network protocols, as MSDE installs by default allowing local
connections only...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Read my How to (Msde related) at:
http://spaces.msn.com/members/sholliday/
12/16/2005 entry date.
The key is the
[Options]
DISABLENETWORKPROTOCOLS=0
SECURITYMODE=SQL
stuff.
Use the KB mentioned earlier, to manually make the fix.
My stuff will teach you how to avoid the issue in the future, if you
ever install MSDE again.
(Or if you get really really stuck and need to start over)
Charles A. Lackman wrote:
> Hello,
> I have MSDE Installed on a different computer (WIndows 2003) within my
> network. I also have MSDE Installed on my production computer for
> development.
> I have successfully created databases on both computers using OSQL. What I
> would really like to accomplish is a connection from the Server Explorer in
> Visual Studio .NET.
> I have a successful connection to the local instance of MSDE and I added my
> server to the server list with no problems.
> But, this is where the problem comes in.
> Lets Say my Server is called Moon, The Servers Tree in Server Explorer
> shows Moon, I can access the Event Logs, Message Queues, Performance
> Counters and Services with on problem. But, when I click on SQL Servers
> Moon is shown with a Red X, I click the Plus (+) sign, type in the Login and
> Password, I receive the following Error:
> {DBNETLIB}{ConnectionOpen (Connect().]SQL Server does not exist or access
> denied.
>
> Any Suggestions will be greatly appreciated,
> Chuck
|||Hello,
I found a way to fix the problem,
Below is what I needed to do.
Run the svrnetcn.exe on which you installed MSDE It will show you SQL Server
Network Utility Dialogbox, Make sure that TCP/IP is enabled and the port is
1433.
Chuck
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O%23rnzwCGGHA.344@.TK2MSFTNGP11.phx.gbl...
Hello,
I have MSDE Installed on a different computer (WIndows 2003) within my
network. I also have MSDE Installed on my production computer for
development.
I have successfully created databases on both computers using OSQL. What I
would really like to accomplish is a connection from the Server Explorer in
Visual Studio .NET.
I have a successful connection to the local instance of MSDE and I added my
server to the server list with no problems.
But, this is where the problem comes in.
Lets Say my Server is called Moon, The Servers Tree in Server Explorer
shows Moon, I can access the Event Logs, Message Queues, Performance
Counters and Services with on problem. But, when I click on SQL Servers
Moon is shown with a Red X, I click the Plus (+) sign, type in the Login and
Password, I receive the following Error:
{DBNETLIB}{ConnectionOpen (Connect().]SQL Server does not exist or access
denied.
Any Suggestions will be greatly appreciated,
Chuck