connections not closed by app

we inherited this java web app that uses a jdbc-odbc bridge for connecting
to 2 sql server 2000 instances. there are probably hundreds of places in
the application where connections are not being closed (resultsets and
statements not being closed as well).
my question:
1) i assume that as DBAs you would all frown on this? do you have
suggestions about doing something on the db side to address this?
2) what i though was something like: does sql server automatically (or can
i make it) close connections after a peroid of time? would that work?Dont know that I would actually do this, but if all else fails:
1; sp_who2 into a temp table
2; have a cursor loop through the LastBatch column, using dynamic sql, and
KILL everything > 5 minutes. (Or whatever time you decide on.)
I would really try to exhaust all other resources before using this method,
just an idea to keep in mind in case you get this deperate.
--
TIA,
ChrisR
"usenetjb" wrote:
> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
>|||Wow. The program must be fixed but for now, I would consider setting up a
schedule to reboot the server at night.
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns97335385B8126usenetjb@.207.115.17.102...
> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?|||"Grant" <email@.nowhere.com> wrote in
news:uUcMfmzBGHA.3572@.TK2MSFTNGP14.phx.gbl:
> Wow. The program must be fixed but for now, I would consider setting
> up a schedule to reboot the server at night.
>
>
wow is not what i said, but then again ... believe it or not the app has
to be up 24/7. so we generally reboot the box after a week to make the app
speed up, sigh. it is leaking resources all over the place on the java app
side.
anyway, can sql server reboot itself? do you have any suggestions in terms
of tools to automate this sql server reboot?
thx

connections not closed by app

we inherited this Java web app that uses a jdbc-odbc bridge for connecting
to 2 sql server 2000 instances. there are probably hundreds of places in
the application where connections are not being closed (resultsets and
statements not being closed as well).
my question:
1) i assume that as DBAs you would all frown on this? do you have
suggestions about doing something on the db side to address this?
2) what i though was something like: does sql server automatically (or can
i make it) close connections after a peroid of time? would that work?Dont know that I would actually do this, but if all else fails:
1; sp_who2 into a temp table
2; have a cursor loop through the LastBatch column, using dynamic sql, and
KILL everything > 5 minutes. (Or whatever time you decide on.)
I would really try to exhaust all other resources before using this method,
just an idea to keep in mind in case you get this deperate.
TIA,
ChrisR
"usenetjb" wrote:

> we inherited this Java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
>|||Wow. The program must be fixed but for now, I would consider setting up a
schedule to reboot the server at night.
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns97335385B8126usenetjb@.207.115.17.102...
> we inherited this Java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?|||"Grant" <email@.nowhere.com> wrote in
news:uUcMfmzBGHA.3572@.TK2MSFTNGP14.phx.gbl:

> Wow. The program must be fixed but for now, I would consider setting
> up a schedule to reboot the server at night.
>
>
wow is not what i said, but then again ... believe it or not the app has
to be up 24/7. so we generally reboot the box after a week to make the app
speed up, sigh. it is leaking resources all over the place on the Java app
side.
anyway, can sql server reboot itself? do you have any suggestions in terms
of tools to automate this sql server reboot?
thx

connections not closed by app

we inherited this java web app that uses a jdbc-odbc bridge for connecting
to 2 sql server 2000 instances. there are probably hundreds of places in
the application where connections are not being closed (resultsets and
statements not being closed as well).
my question:
1) i assume that as DBAs you would all frown on this? do you have
suggestions about doing something on the db side to address this?
2) what i though was something like: does sql server automatically (or can
i make it) close connections after a peroid of time? would that work?
Dont know that I would actually do this, but if all else fails:
1; sp_who2 into a temp table
2; have a cursor loop through the LastBatch column, using dynamic sql, and
KILL everything > 5 minutes. (Or whatever time you decide on.)
I would really try to exhaust all other resources before using this method,
just an idea to keep in mind in case you get this deperate.
TIA,
ChrisR
"usenetjb" wrote:

> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
>
|||Wow. The program must be fixed but for now, I would consider setting up a
schedule to reboot the server at night.
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns97335385B8126usenetjb@.207.115.17.102...
> we inherited this java web app that uses a jdbc-odbc bridge for connecting
> to 2 sql server 2000 instances. there are probably hundreds of places in
> the application where connections are not being closed (resultsets and
> statements not being closed as well).
> my question:
> 1) i assume that as DBAs you would all frown on this? do you have
> suggestions about doing something on the db side to address this?
> 2) what i though was something like: does sql server automatically (or can
> i make it) close connections after a peroid of time? would that work?
|||"Grant" <email@.nowhere.com> wrote in
news:uUcMfmzBGHA.3572@.TK2MSFTNGP14.phx.gbl:

> Wow. The program must be fixed but for now, I would consider setting
> up a schedule to reboot the server at night.
>
>
wow is not what i said, but then again ... believe it or not the app has
to be up 24/7. so we generally reboot the box after a week to make the app
speed up, sigh. it is leaking resources all over the place on the java app
side.
anyway, can sql server reboot itself? do you have any suggestions in terms
of tools to automate this sql server reboot?
thx

Connectioni leak with MDAC 2.82

Hi, I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousends). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).

Is there a knwon bug like this? Is there a way to trace the ADO provider?are you sure you are explicitly closing your connections?|||What is your code that opens and closes connection to the server?|||

Hi, sorry for the delay, the forum alert seems not to work for me either :-)

Yes. I am closing all connections, the program leaves only about 5-10% of the connections behind, not all of them. Also, I run the same code using ODBC provider and that works absolutely fine. I my code there is a chance to leave connection behind in case of error, but than I would see error messages inmy log, which I don't. Here is the code:

_ConnectionPtr
AsyncOpen(struct s_profiles *profile)
{
time_t start;
string sConnStr;
HRESULT hr;
SYSTEMTIME st1,st2;
_ConnectionPtr pConn = NULL;
char msg[1024];
try {
start = time(NULL);
GetSystemTime(&st1); //time before connect
TESTHR(pConn.CreateInstance(__uuidof(Connection)));
pConn->ConnectionTimeout = profile->conn->timeout; //set ADO Connection Timeout (default 30sec)
pConn->CommandTimeout = profile->timeout; //set ADO Command Timeout (default 30sec)

if (pConn->State == adStateOpen) {
nimLog(1,"Profile: %s, connection: %s already opem", profile->name, profile->connection);
return pConn; //Already open
}

if (strcmp(profile->cursor,"server") == 0)
pConn->CursorLocation = adUseServer; //use server cursor
else
pConn->CursorLocation = adUseClient; //use client cursor (default)

sConnStr = CreateConnString(profile->conn);

hr = pConn->Open(_bstr_t(sConnStr.c_str()),bsNull,bsNull,adAsyncConnect);
if (FAILED(hr))
{
nimLog(0,"Profile %s, AsyncOpen - failed for connection '%s'",profile->name, profile->connection);
return pConn;
}

while (pConn->State == adStateConnecting)
{
//nimLog(0,"Profile: %s - Wait for Connection to open", profile->name);
if(time(NULL) > (start + profile->conn->timeout + gSetup->COM_tout_delay))
{
nimLog(0,"Profile %s, AsyncOpen - connection timeout",profile->name;
pConn->Cancel();
pConn = NULL;
return pConn;
}
Sleep(5);
}
nimLog(2,"Profile %s, AsyncOpen - connected to database",profile->name);

if (FAILED(hr))
{
nimLog(0,"Open - failed for connection '%s'",profile->connection);
return 0;
}
}
catch(_com_error &e ) {
nimLog(0,"Profile: %s, Error in AsyncOpen", profile->name);
LogComError(e, profile->name, 0, "Open");
return 0;
}
catch(...){
nimLog(0,"Profile: %s, Unknown error in Open", profile->name);
sprintf(msg,"Profile %s, unknown error in Open", profile->name);
return 0;
}
//return (pConn->State == adStateOpen ? 1 : 0);
return pConn;
}

//close

try
{
hr = pConn->Close();
if (FAILED(hr))
{
nimLog(0,"Close connection failed (%s:%d)",__FILE__,__LINE__);
EXIT_THREAD(0);
}
pConn = NULL;
}

Thanks for help!

|||Is Cancel method same as Close for the ADO Connection in C++? I do not know C++, but if this is the case, then code looks good. What you could do is to run SQL Profiler to trace activity between the server and your application. It could provide additional information as well|||

Close and Cancel are not exactly the same, Cancel is used to close an asynchronous request, Close to end after you're done. I am using asynchronous Open to be able to bail out if timeout reach, than I need Cancel.

To use the Profiler shows, unfortunately only half of the story. I can see what the ADO provider send towards SQL, but not what my application sends to ADO. At the end I can see every once in a whole there is a connection not closed, but no idea why - did the application not sent the close? Not to be seen in profiler. And ODBC like trace does not exeists, at least to my knowledge.

I am believing now there is something wrong with the ADO provider on that one machine, as it doens't happen on others and also it works there with ODBC. The question remains what is wrong, I cannot see anything obvious. I only observed, that the response time with ADO was considerable slower than with ODBC, althoug I would expect it the other way around.

Any other ideas out there?

|||It is possible that Cancel is not enough. Can you try to use Close to see if it helps or you cannot?|||Unfortunately, it is a production machine where it happens, and I am limited in my activities there. But on my test machine Cancel works, quite the oposite, Close doesn't work there. But to this part of code I get only in "timeout" situation, where I get a message - I didn't get any timeout at all yet on the production machine. I would say it must be something else.|||

I am wondering if your ADO connection is actually getting pooled.

Add "Pooling=false;" to your ado connection string. If the problem goes away, its not an issue, remove the keyword/value pair and carry on. If the problem persists, I am wondering if you need an analog to your _ConnectionPtr->CreateInstance that releases the instance?

|||Make sure that the connection is closed--even if the code throws an exception.|||

Hi Dbrich,

Same problem what you faced long back ago.

I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousands). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790),SP4 for SQL Server.

actually when i run the application on my machine i didn't find any connection leak but on production machine i find connection leak.

Is there a know bug like this? Is there a way to trace the ADO provider?

In my SQL Profiler I got this error number 16945,16955,208

Connectioni leak with MDAC 2.82

Hi, I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousends). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790).

Is there a knwon bug like this? Is there a way to trace the ADO provider?are you sure you are explicitly closing your connections?|||What is your code that opens and closes connection to the server?|||

Hi, sorry for the delay, the forum alert seems not to work for me either :-)

Yes. I am closing all connections, the program leaves only about 5-10% of the connections behind, not all of them. Also, I run the same code using ODBC provider and that works absolutely fine. I my code there is a chance to leave connection behind in case of error, but than I would see error messages inmy log, which I don't. Here is the code:

_ConnectionPtr
AsyncOpen(struct s_profiles *profile)
{
time_t start;
string sConnStr;
HRESULT hr;
SYSTEMTIME st1,st2;
_ConnectionPtr pConn = NULL;
char msg[1024];
try {
start = time(NULL);
GetSystemTime(&st1); //time before connect
TESTHR(pConn.CreateInstance(__uuidof(Connection)));
pConn->ConnectionTimeout = profile->conn->timeout; //set ADO Connection Timeout (default 30sec)
pConn->CommandTimeout = profile->timeout; //set ADO Command Timeout (default 30sec)

if (pConn->State == adStateOpen) {
nimLog(1,"Profile: %s, connection: %s already opem", profile->name, profile->connection);
return pConn; //Already open
}

if (strcmp(profile->cursor,"server") == 0)
pConn->CursorLocation = adUseServer; //use server cursor
else
pConn->CursorLocation = adUseClient; //use client cursor (default)

sConnStr = CreateConnString(profile->conn);

hr = pConn->Open(_bstr_t(sConnStr.c_str()),bsNull,bsNull,adAsyncConnect);
if (FAILED(hr))
{
nimLog(0,"Profile %s, AsyncOpen - failed for connection '%s'",profile->name, profile->connection);
return pConn;
}

while (pConn->State == adStateConnecting)
{
//nimLog(0,"Profile: %s - Wait for Connection to open", profile->name);
if(time(NULL) > (start + profile->conn->timeout + gSetup->COM_tout_delay))
{
nimLog(0,"Profile %s, AsyncOpen - connection timeout",profile->name;
pConn->Cancel();
pConn = NULL;
return pConn;
}
Sleep(5);
}
nimLog(2,"Profile %s, AsyncOpen - connected to database",profile->name);

if (FAILED(hr))
{
nimLog(0,"Open - failed for connection '%s'",profile->connection);
return 0;
}
}
catch(_com_error &e ) {
nimLog(0,"Profile: %s, Error in AsyncOpen", profile->name);
LogComError(e, profile->name, 0, "Open");
return 0;
}
catch(...){
nimLog(0,"Profile: %s, Unknown error in Open", profile->name);
sprintf(msg,"Profile %s, unknown error in Open", profile->name);
return 0;
}
//return (pConn->State == adStateOpen ? 1 : 0);
return pConn;
}

//close

try
{
hr = pConn->Close();
if (FAILED(hr))
{
nimLog(0,"Close connection failed (%s:%d)",__FILE__,__LINE__);
EXIT_THREAD(0);
}
pConn = NULL;
}

Thanks for help!

|||Is Cancel method same as Close for the ADO Connection in C++? I do not know C++, but if this is the case, then code looks good. What you could do is to run SQL Profiler to trace activity between the server and your application. It could provide additional information as well|||

Close and Cancel are not exactly the same, Cancel is used to close an asynchronous request, Close to end after you're done. I am using asynchronous Open to be able to bail out if timeout reach, than I need Cancel.

To use the Profiler shows, unfortunately only half of the story. I can see what the ADO provider send towards SQL, but not what my application sends to ADO. At the end I can see every once in a whole there is a connection not closed, but no idea why - did the application not sent the close? Not to be seen in profiler. And ODBC like trace does not exeists, at least to my knowledge.

I am believing now there is something wrong with the ADO provider on that one machine, as it doens't happen on others and also it works there with ODBC. The question remains what is wrong, I cannot see anything obvious. I only observed, that the response time with ADO was considerable slower than with ODBC, althoug I would expect it the other way around.

Any other ideas out there?

|||It is possible that Cancel is not enough. Can you try to use Close to see if it helps or you cannot?|||Unfortunately, it is a production machine where it happens, and I am limited in my activities there. But on my test machine Cancel works, quite the oposite, Close doesn't work there. But to this part of code I get only in "timeout" situation, where I get a message - I didn't get any timeout at all yet on the production machine. I would say it must be something else.|||

I am wondering if your ADO connection is actually getting pooled.

Add "Pooling=false;" to your ado connection string. If the problem goes away, its not an issue, remove the keyword/value pair and carry on. If the problem persists, I am wondering if you need an analog to your _ConnectionPtr->CreateInstance that releases the instance?

|||Make sure that the connection is closed--even if the code throws an exception.|||

Hi Dbrich,

Same problem what you faced long back ago.

I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousands). It is using the SQL ADO provider, MDAC 2.82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790),SP4 for SQL Server.

actually when i run the application on my machine i didn't find any connection leak but on production machine i find connection leak.

Is there a know bug like this? Is there a way to trace the ADO provider?

In my SQL Profiler I got this error number 16945,16955,208