32000 User Connections (According to Performance Monitor)

Hello,
I am dealing with a quite big application written in ASP communicating
directly with the database. The problem is that there is performance
problems with the DB and after investigating things a bit more I have
found that the number of User Connection are over 32000 according to
the Performance Monitor.
Yes, it would be better too have a 3-tier architecture but I am stuck
with what I have and have to do the best of the situation. I have gone
through every page to make sure that the ADO connection is closed and
that the connection object is set to nothing. I have also tried to
change the MAX Pool Size from everything from 50 upp to 500 and
Connection Lifetime I have set to 10. My conclusion is that I am
dealing with a leak somewhere (but I can not find it). My question is,
is there a way in SQL Server to clean up un-used user connection - let
them time out by them self.
Any suggestions? Anybody encountered anything similar? All ideas and
comments are welcomed.You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
<mathias_pettersson@.hotmail.com> wrote in message
news:1137510134.733734.249390@.g49g2000cwa.googlegroups.com...
> Hello,
> I am dealing with a quite big application written in ASP communicating
> directly with the database. The problem is that there is performance
> problems with the DB and after investigating things a bit more I have
> found that the number of User Connection are over 32000 according to
> the Performance Monitor.
> Yes, it would be better too have a 3-tier architecture but I am stuck
> with what I have and have to do the best of the situation. I have gone
> through every page to make sure that the ADO connection is closed and
> that the connection object is set to nothing. I have also tried to
> change the MAX Pool Size from everything from 50 upp to 500 and
> Connection Lifetime I have set to 10. My conclusion is that I am
> dealing with a leak somewhere (but I can not find it). My question is,
> is there a way in SQL Server to clean up un-used user connection - let
> them time out by them self.
> Any suggestions? Anybody encountered anything similar? All ideas and
> comments are welcomed.
>|||Just out of interest how much memory does your server have..?
Each connection takes around 24KB, you've got 24Kb * 320000 which is about
750Mb gone from SQL Server.
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uoYfth3GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> You could create a job that took the data from sp_who2 and compared the
> time
> now to the LastBatch column. If they exceed your threshold disconnect them
> with the KILL command.
> Ensure your application can handle this and exit gracefully.
> --
> HTH. Ryan
> <mathias_pettersson@.hotmail.com> wrote in message
> news:1137510134.733734.249390@.g49g2000cwa.googlegroups.com...
>|||The server has 1GB of RAM and dual 2.4Ghz processors

32000 User Connections (According to Performance Monitor)

Hello,
I am dealing with a quite big application written in ASP communicating
directly with the database. The problem is that there is performance
problems with the DB and after investigating things a bit more I have
found that the number of User Connection are over 32000 according to
the Performance Monitor.
Yes, it would be better too have a 3-tier architecture but I am stuck
with what I have and have to do the best of the situation. I have gone
through every page to make sure that the ADO connection is closed and
that the connection object is set to nothing. I have also tried to
change the MAX Pool Size from everything from 50 upp to 500 and
Connection Lifetime I have set to 10. My conclusion is that I am
dealing with a leak somewhere (but I can not find it). My question is,
is there a way in SQL Server to clean up un-used user connection - let
them time out by them self.
Any suggestions? Anybody encountered anything similar? All ideas and
comments are welcomed.
You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
HTH. Ryan
<mathias_pettersson@.hotmail.com> wrote in message
news:1137510134.733734.249390@.g49g2000cwa.googlegr oups.com...
> Hello,
> I am dealing with a quite big application written in ASP communicating
> directly with the database. The problem is that there is performance
> problems with the DB and after investigating things a bit more I have
> found that the number of User Connection are over 32000 according to
> the Performance Monitor.
> Yes, it would be better too have a 3-tier architecture but I am stuck
> with what I have and have to do the best of the situation. I have gone
> through every page to make sure that the ADO connection is closed and
> that the connection object is set to nothing. I have also tried to
> change the MAX Pool Size from everything from 50 upp to 500 and
> Connection Lifetime I have set to 10. My conclusion is that I am
> dealing with a leak somewhere (but I can not find it). My question is,
> is there a way in SQL Server to clean up un-used user connection - let
> them time out by them self.
> Any suggestions? Anybody encountered anything similar? All ideas and
> comments are welcomed.
>
|||Just out of interest how much memory does your server have..?
Each connection takes around 24KB, you've got 24Kb * 320000 which is about
750Mb gone from SQL Server.
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uoYfth3GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> You could create a job that took the data from sp_who2 and compared the
> time
> now to the LastBatch column. If they exceed your threshold disconnect them
> with the KILL command.
> Ensure your application can handle this and exit gracefully.
> --
> HTH. Ryan
> <mathias_pettersson@.hotmail.com> wrote in message
> news:1137510134.733734.249390@.g49g2000cwa.googlegr oups.com...
>
|||The server has 1GB of RAM and dual 2.4Ghz processors

32000 User Connections (According to Performance Monitor)

Hello,
I am dealing with a quite big application written in ASP communicating
directly with the database. The problem is that there is performance
problems with the DB and after investigating things a bit more I have
found that the number of User Connection are over 32000 according to
the Performance Monitor.
Yes, it would be better too have a 3-tier architecture but I am stuck
with what I have and have to do the best of the situation. I have gone
through every page to make sure that the ADO connection is closed and
that the connection object is set to nothing. I have also tried to
change the MAX Pool Size from everything from 50 upp to 500 and
Connection Lifetime I have set to 10. My conclusion is that I am
dealing with a leak somewhere (but I can not find it). My question is,
is there a way in SQL Server to clean up un-used user connection - let
them time out by them self.
Any suggestions? Anybody encountered anything similar? All ideas and
comments are welcomed.You could create a job that took the data from sp_who2 and compared the time
now to the LastBatch column. If they exceed your threshold disconnect them
with the KILL command.
Ensure your application can handle this and exit gracefully.
--
HTH. Ryan
<mathias_pettersson@.hotmail.com> wrote in message
news:1137510134.733734.249390@.g49g2000cwa.googlegroups.com...
> Hello,
> I am dealing with a quite big application written in ASP communicating
> directly with the database. The problem is that there is performance
> problems with the DB and after investigating things a bit more I have
> found that the number of User Connection are over 32000 according to
> the Performance Monitor.
> Yes, it would be better too have a 3-tier architecture but I am stuck
> with what I have and have to do the best of the situation. I have gone
> through every page to make sure that the ADO connection is closed and
> that the connection object is set to nothing. I have also tried to
> change the MAX Pool Size from everything from 50 upp to 500 and
> Connection Lifetime I have set to 10. My conclusion is that I am
> dealing with a leak somewhere (but I can not find it). My question is,
> is there a way in SQL Server to clean up un-used user connection - let
> them time out by them self.
> Any suggestions? Anybody encountered anything similar? All ideas and
> comments are welcomed.
>|||Just out of interest how much memory does your server have..?
Each connection takes around 24KB, you've got 24Kb * 320000 which is about
750Mb gone from SQL Server.
--
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uoYfth3GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> You could create a job that took the data from sp_who2 and compared the
> time
> now to the LastBatch column. If they exceed your threshold disconnect them
> with the KILL command.
> Ensure your application can handle this and exit gracefully.
> --
> HTH. Ryan
> <mathias_pettersson@.hotmail.com> wrote in message
> news:1137510134.733734.249390@.g49g2000cwa.googlegroups.com...
>> Hello,
>> I am dealing with a quite big application written in ASP communicating
>> directly with the database. The problem is that there is performance
>> problems with the DB and after investigating things a bit more I have
>> found that the number of User Connection are over 32000 according to
>> the Performance Monitor.
>> Yes, it would be better too have a 3-tier architecture but I am stuck
>> with what I have and have to do the best of the situation. I have gone
>> through every page to make sure that the ADO connection is closed and
>> that the connection object is set to nothing. I have also tried to
>> change the MAX Pool Size from everything from 50 upp to 500 and
>> Connection Lifetime I have set to 10. My conclusion is that I am
>> dealing with a leak somewhere (but I can not find it). My question is,
>> is there a way in SQL Server to clean up un-used user connection - let
>> them time out by them self.
>> Any suggestions? Anybody encountered anything similar? All ideas and
>> comments are welcomed.
>|||The server has 1GB of RAM and dual 2.4Ghz processorssql

32 bit SQLDMO code on a 64 bit server

Should an application written in VB6 and early bound to SQL Server 2000
SQLDMO run on a machine with 64 bit SQL Server 2000 installed?
We update SQL databases with a custom written in house VB6 app. What would
be involved in getting this program to run in this environment?
Thanks for any input.Hi
VB 6 is a 32 bit applicatzion and will not run on 64 bit Itanium I/II's as
the Itanium is a lot different in architecture to the existing 32 bit
processor.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Scott Duckworth" wrote:

> Should an application written in VB6 and early bound to SQL Server 2000
> SQLDMO run on a machine with 64 bit SQL Server 2000 installed?
> We update SQL databases with a custom written in house VB6 app. What would
> be involved in getting this program to run in this environment?
> Thanks for any input.
>
>|||Thanks for the info.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:DF34C2FB-F59D-48F8-BBA5-D57339C384B9@.microsoft.com...
> Hi
> VB 6 is a 32 bit applicatzion and will not run on 64 bit Itanium I/II's as
> the Itanium is a lot different in architecture to the existing 32 bit
> processor.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Scott Duckworth" wrote:
>
would|||There is such a thing as WOW64 hosts 32-bit Windows applications. If you are
not running Windows Server 2003 SP1, you should use the IA32 extension
layer.
However Microsoft does not support running the SQL Server 2000 tools in
WOW64 on Itanium, which does not mean it does not work, it does.
So you would have to install the 32-bit client tools on your Itanium, which
will get you SQL-DMO, then you need your VB6 application and VB6 runtime.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:DF34C2FB-F59D-48F8-BBA5-D57339C384B9@.microsoft.com...
> Hi
> VB 6 is a 32 bit applicatzion and will not run on 64 bit Itanium I/II's as
> the Itanium is a lot different in architecture to the existing 32 bit
> processor.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Scott Duckworth" wrote:
>

24000 Invalid cursor state. Prepared Statement

I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++.

I am not using / importing any dlls.

I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery.

I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed.

First time when I bind paramaters using SQLBindParameter it works perfect.

When loop gets executed secondtime onwards, it gives an error.
SQLState: 24000 [ODBC Client Interface]Invalid cursor state.

If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error.

I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time.

Have I missed any step?
Where & when I should code the cursor type? Any specific libraries I need to link?

Thanksyes you missed something. see brett's sticky at the top of the page.|||I traced the solution.

I thought that the mistake is in declaring scrollable cursors.

So I explained precisouly the problem in 7-8 lines.
Reading 50-60 lines with altogether different coding standards is difficult.

Its not coding problem but associated with scrollable cursor options.
So I did not include the code.

MSDN examples does not show such required step as the scope of example code gets over before such situation is reached!!.

2005: creating aggregate function from .NET assembly

Hello,
I am learning SQL Server 2005. I have (correctly) written in .NET
assembly DemoSQLServer with aggregate function AvgNoMinMax in class
Demo and I have added assembly to database DemoSQLServer. Now I need
to create aggregate in SQL Server. I tried this way:

CREATE AGGREGATE AvgNoMinMax(@.v float) RETURNS float EXTERNAL NAME
[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]

Unfortunately I have error:

Incorrect syntax near '.'.

I don't know what's wrong. Please help.
Thank you very much!
/RAM/R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

I am learning SQL Server 2005. I have (correctly) written in .NET
assembly DemoSQLServer with aggregate function AvgNoMinMax in class
Demo and I have added assembly to database DemoSQLServer. Now I need
to create aggregate in SQL Server. I tried this way:
>
CREATE AGGREGATE AvgNoMinMax(@.v float) RETURNS float EXTERNAL NAME
[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]
>
Unfortunately I have error:
>
Incorrect syntax near '.'.

The thing to do when you get a syntax error is to look up the topic
for the command in question and study the syntax graph in detail to
find out what's wrong.

Yes, I'm telling you to read the manual. You see, you are learning SQL 2005,
and part of that is to learn the tremendous asset that Books Online actually
is. Besides, by doing mistakes and finding what mistake - that is how you
really learn things. You don't learn things by being spoon-fed in
newsgroups.

(That is not to say that asking in newsgroups is a bad idea. Sometimes
it's rather creativity you need help with, and that is not be found in
Books Online.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx