TCL, ODBC and SQL Server 7.0 - Database server crashing 
Author Message
 TCL, ODBC and SQL Server 7.0 - Database server crashing

Hi all,

I've been handed a legacy TCL application using TCL 8.04 core
and TCLODBC.DLL version 2.2 from
http://www.*-*-*.com/ ~rnurmi/tclodbc.html, running on
Windows NT 4.0, SP5

The application is web-based using IIS, and dynamically generates web
pages from a SQL Server 7.0 database (running on a separate server).

The application is load-balanced across 3 web servers, talking to
a single SQL database server.

The problem we are facing is that after the application has been
running on the web servers for a while (IIS 4.0, NT4.0 SP5), the
SQL Server machine sucks up all its memory and then the CPU redlines
at 100%.  Attempts to access the webpages start to get timeout errors
- the database stops responding.  If we stop and restart the
SQL Server service under NT (on the database server),
it clears the memory and the application can run as normal,
for a while....  The webserver farm gets about 300K pageviews
a week, and we have to restart the SQL Server service every
few hours right now - which is NOT a good thing.  Are there
any known problems with TCL ODBC connections
and SQL Server 7.0?  I have searched the usenet and TCL archives and
have come up empty.  It makes me think that there  is a coding
or configuration error, since no one else seems to have reported
this problem.

other details of the configuration are as follows:

Client Network Protocol; and specifically communication to SQL Server:
ODBC TCP/IP Network Library

SQL Server ODBC Driver file name, version, date, size:
ODBC Driver: SQL Server, Version 3.70.08.20, Date 10/05/99
Driver File: SQLSRV32.DLL, 520464 bytes,  dated 10/22/99

Any and all suggestions or info would be most helpful

-Natalie

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Sun, 09 Mar 2003 03:00:00 GMT  
 TCL, ODBC and SQL Server 7.0 - Database server crashing
Natalie,

Questions, questions, questions :-)

   Is your code a cgi app? fast-cgi? or what?  If it's persistent, then
are you using some sort of connection pooling to the dB?  If not, are
you closing out your connections after the app is done with it?  How
many conections per app are being made to the dB, i.e. during that page
hit?  What does the dB log say, any errors?  Logs ok, as in not full?
Temp dB, how does it stand during this time?  Are you doing any big
selects/updates/etc... that would lock a lot of rows, and eatup temp dB
and or the logs?  If you are fetching back a lot of data, are you doing:
set ResultSetList [$dbhnd $sqlstr] to get a massive amount of data, or a
$dbhnd statement dbhnd_stmt $sqlstr/$dbstmthnd fetch combo?  How much
memory is in the dB server?  Are there any other apps running on the dB
server box?

These are all things I have had problems with in the past with m$-sql
server, tho none since 7.0 came out (It fixed many sins)

scott



Quote:
> Hi all,

> I've been handed a legacy TCL application using TCL 8.04 core
> and TCLODBC.DLL version 2.2 from
> http://megamail.solagem.fi/+AH4-rnurmi/tclodbc.html, running on
> Windows NT 4.0, SP5

> The application is web-based using IIS, and dynamically generates web
> pages from a SQL Server 7.0 database (running on a separate server).

> The application is load-balanced across 3 web servers, talking to
> a single SQL database server.

> The problem we are facing is that after the application has been
> running on the web servers for a while (IIS 4.0, NT4.0 SP5), the
> SQL Server machine sucks up all its memory and then the CPU redlines
> at 100%.  Attempts to access the webpages start to get timeout errors
> - the database stops responding.  If we stop and restart the
> SQL Server service under NT (on the database server),
> it clears the memory and the application can run as normal,
> for a while....  The webserver farm gets about 300K pageviews
> a week, and we have to restart the SQL Server service every
> few hours right now - which is NOT a good thing.  Are there
> any known problems with TCL ODBC connections
> and SQL Server 7.0?  I have searched the usenet and TCL archives and
> have come up empty.  It makes me think that there  is a coding
> or configuration error, since no one else seems to have reported
> this problem.

> other details of the configuration are as follows:

> Client Network Protocol; and specifically communication to SQL Server:
> ODBC TCP/IP Network Library

> SQL Server ODBC Driver file name, version, date, size:
> ODBC Driver: SQL Server, Version 3.70.08.20, Date 10/05/99
> Driver File: SQLSRV32.DLL, 520464 bytes,  dated 10/22/99

> Any and all suggestions or info would be most helpful

> -Natalie

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Sun, 09 Mar 2003 03:00:00 GMT  
 TCL, ODBC and SQL Server 7.0 - Database server crashing
One more question:
   How are you making your connection to the dB?

Via:
   database dbhnd $datasrcname $userid $password
or
   database dbhnd $connectstr

If it's the first with the datasrcname, do you have it set NOT to create
a temp stored proc for the sql.  I have had problems with this eating
away at temp dB, and killing the server in the way you spoke of, but not
with 7.0.  An idea anyway.

scott


Quote:

> Natalie,

> Questions, questions, questions :-)

>    Is your code a cgi app? fast-cgi? or what?  If it's persistent,
then
> are you using some sort of connection pooling to the dB?  If not, are
> you closing out your connections after the app is done with it?  How
> many conections per app are being made to the dB, i.e. during that
page
> hit?  What does the dB log say, any errors?  Logs ok, as in not full?
> Temp dB, how does it stand during this time?  Are you doing any big
> selects/updates/etc... that would lock a lot of rows, and eatup temp
dB
> and or the logs?  If you are fetching back a lot of data, are you
doing:
> set ResultSetList [$dbhnd $sqlstr] to get a massive amount of data, or
a
> $dbhnd statement dbhnd_stmt $sqlstr/$dbstmthnd fetch combo?  How much
> memory is in the dB server?  Are there any other apps running on the
dB
> server box?

> These are all things I have had problems with in the past with m$-sql
> server, tho none since 7.0 came out (It fixed many sins)

> scott



> > Hi all,

> > I've been handed a legacy TCL application using TCL 8.04 core
> > and TCLODBC.DLL version 2.2 from
> > http://megamail.solagem.fi/+AH4-rnurmi/tclodbc.html, running on
> > Windows NT 4.0, SP5

> > The application is web-based using IIS, and dynamically generates
web
> > pages from a SQL Server 7.0 database (running on a separate server).

> > The application is load-balanced across 3 web servers, talking to
> > a single SQL database server.

> > The problem we are facing is that after the application has been
> > running on the web servers for a while (IIS 4.0, NT4.0 SP5), the
> > SQL Server machine sucks up all its memory and then the CPU redlines
> > at 100%.  Attempts to access the webpages start to get timeout
errors
> > - the database stops responding.  If we stop and restart the
> > SQL Server service under NT (on the database server),
> > it clears the memory and the application can run as normal,
> > for a while....  The webserver farm gets about 300K pageviews
> > a week, and we have to restart the SQL Server service every
> > few hours right now - which is NOT a good thing.  Are there
> > any known problems with TCL ODBC connections
> > and SQL Server 7.0?  I have searched the usenet and TCL archives and
> > have come up empty.  It makes me think that there  is a coding
> > or configuration error, since no one else seems to have reported
> > this problem.

> > other details of the configuration are as follows:

> > Client Network Protocol; and specifically communication to SQL
Server:
> > ODBC TCP/IP Network Library

> > SQL Server ODBC Driver file name, version, date, size:
> > ODBC Driver: SQL Server, Version 3.70.08.20, Date 10/05/99
> > Driver File: SQLSRV32.DLL, 520464 bytes,  dated 10/22/99

> > Any and all suggestions or info would be most helpful

> > -Natalie

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 10 Mar 2003 03:00:00 GMT  
 TCL, ODBC and SQL Server 7.0 - Database server crashing
Yet another question :-)
  Are there any other dB apps running against that dB server at the same
time?  If so how are they connecting, and what are they doing?

scott


Quote:

> One more question:
>    How are you making your connection to the dB?

> Via:
>    database dbhnd $datasrcname $userid $password
> or
>    database dbhnd $connectstr

> If it's the first with the datasrcname, do you have it set NOT to
create
> a temp stored proc for the sql.  I have had problems with this eating
> away at temp dB, and killing the server in the way you spoke of, but
not
> with 7.0.  An idea anyway.

> scott



> > Natalie,

> > Questions, questions, questions :-)

> >    Is your code a cgi app? fast-cgi? or what?  If it's persistent,
> then
> > are you using some sort of connection pooling to the dB?  If not,
are
> > you closing out your connections after the app is done with it?  How
> > many conections per app are being made to the dB, i.e. during that
> page
> > hit?  What does the dB log say, any errors?  Logs ok, as in not
full?
> > Temp dB, how does it stand during this time?  Are you doing any big
> > selects/updates/etc... that would lock a lot of rows, and eatup temp
> dB
> > and or the logs?  If you are fetching back a lot of data, are you
> doing:
> > set ResultSetList [$dbhnd $sqlstr] to get a massive amount of data,
or
> a
> > $dbhnd statement dbhnd_stmt $sqlstr/$dbstmthnd fetch combo?  How
much
> > memory is in the dB server?  Are there any other apps running on the
> dB
> > server box?

> > These are all things I have had problems with in the past with
m$-sql
> > server, tho none since 7.0 came out (It fixed many sins)

> > scott



> > > Hi all,

> > > I've been handed a legacy TCL application using TCL 8.04 core
> > > and TCLODBC.DLL version 2.2 from
> > > http://megamail.solagem.fi/+AH4-rnurmi/tclodbc.html, running on
> > > Windows NT 4.0, SP5

> > > The application is web-based using IIS, and dynamically generates
> web
> > > pages from a SQL Server 7.0 database (running on a separate
server).

> > > The application is load-balanced across 3 web servers, talking to
> > > a single SQL database server.

> > > The problem we are facing is that after the application has been
> > > running on the web servers for a while (IIS 4.0, NT4.0 SP5), the
> > > SQL Server machine sucks up all its memory and then the CPU
redlines
> > > at 100%.  Attempts to access the webpages start to get timeout
> errors
> > > - the database stops responding.  If we stop and restart the
> > > SQL Server service under NT (on the database server),
> > > it clears the memory and the application can run as normal,
> > > for a while....  The webserver farm gets about 300K pageviews
> > > a week, and we have to restart the SQL Server service every
> > > few hours right now - which is NOT a good thing.  Are there
> > > any known problems with TCL ODBC connections
> > > and SQL Server 7.0?  I have searched the usenet and TCL archives
and
> > > have come up empty.  It makes me think that there  is a coding
> > > or configuration error, since no one else seems to have reported
> > > this problem.

> > > other details of the configuration are as follows:

> > > Client Network Protocol; and specifically communication to SQL
> Server:
> > > ODBC TCP/IP Network Library

> > > SQL Server ODBC Driver file name, version, date, size:
> > > ODBC Driver: SQL Server, Version 3.70.08.20, Date 10/05/99
> > > Driver File: SQLSRV32.DLL, 520464 bytes,  dated 10/22/99

> > > Any and all suggestions or info would be most helpful

> > > -Natalie

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 10 Mar 2003 03:00:00 GMT  
 TCL, ODBC and SQL Server 7.0 - Database server crashing
Scott,

Thanks for your comments and questions!  I'll answer them to the
best of my ability.

Quote:
>   Are there any other dB apps running against that dB
> server at the same time?  If so how are they connecting,
> and what are they doing?

There is a TCL win32 console app running against the database connecting
in the same way as the web page generator applications.  The console
app runs every 5 minutes on one of the web servers.

We also have cold fusion application servers connecting for updates
and reads, but they use Allaire's ODBC drivers, connection pooling,
and limit the number of active threads to the database to 5 per
application server (there are 3 CF servers)

Quote:
> >    How are you making your connection to the dB?

> > Via:
> >    database dbhnd $datasrcname $userid $password

The one listed above  ^^^

Quote:
> > or
> >    database dbhnd $connectstr

> > If it's the first with the datasrcname, do you have it set NOT to
> > create a temp stored proc for the sql.  I have had problems with
> > this eating away at temp dB, and killing the server in the
> > way you spoke of, but not with 7.0.  An idea anyway.

I'm not sure if it does this or not.
What would you have to do in code (or configuration) to NOT
create a temp stored proc? Or conversely, what would you have to do in
code or configuration files TO create temp stored procedures for the
SQL?

Quote:
> Is your code a cgi app? fast-cgi? or what?

CGI.  I was told by the developer that FAST-CGI only works with Apache.
I have not had time to verify this.

Quote:
> If it's persistent, then are you using some sort of connection
> pooling to the dB?

It is not persistent and to the best of my knowledge we are not using
connection pooling.  Is there some way we can use connection pooling
from a TCL cgi running under IIS?  Is there a way to convert the
TCL interpreter into an IIS dll so that it can be persistent?

Quote:
>If not, are you closing out your connections after the app is done
> with it?

the application wasn't doing this - it was letting the TCL interpreter
exit clean up and close connections.  We put in explicit disconnects
and it made no difference.

Quote:
> How many conections per app are being made to the dB, i.e. during
> that page hit?

Between 2 and 4 connections.

Quote:
>What does the dB log say, any errors?  Logs ok, as in not full?

No errors that we can identify as affecting this performance problem
and the logs are not full.

Quote:
> Temp dB, how does it stand during this time?

It gets big. but we have 1.5GB of memory.

Quote:
>Are you doing any big selects/updates/etc... that would lock a lot
> of rows, and eatup temp dB

Yes.  and the console app that runs every 5 minutes also does large
selects.  As far as I know, because of security settings, it is
doing a read committed every time.

Quote:
> and or the logs?  If you are fetching back a lot of data, are you
> doing:
>    set ResultSetList [$dbhnd $sqlstr] to get a massive amount of data,

We are doing the above (for the most part)

Quote:
> or
>    a
>    $dbhnd statement dbhnd_stmt $sqlstr/$dbstmthnd fetch combo?

What is the difference in terms of affect on performance?

Quote:
> How much memory is in the dB server?

1.5 GB

Quote:
>Are there any other apps running on the dB  server box?

Not directly on the box itself, but on the other servers and connecting
to the DB.

Quote:
> These are all things I have had problems with in the past with
> m$-sql server, tho none since 7.0 came out (It fixed many sins)

Is it that you haven't tried the things that used to fail under 6.5
or that you have, and they don't cause the same problems anymore?

This has all the hallmarks of the application servers
leaving connections open to the database and not closing them,
though when the server fails we see between 25 and 30 active
connections.  It never seems to increase beyond that, and I don't
even know if that is excessive or reasonable....

Any suggestions for things we could try would really help.
Our former TCL developer is no longer here, and the current
developer is not a TCL expert, so we are scrambling to fix
this.

Thanks,
-Natalie

Sent via Deja.com http://www.deja.com/
Before you buy.



Tue, 11 Mar 2003 03:00:00 GMT  
 TCL, ODBC and SQL Server 7.0 - Database server crashing


Quote:
> There is a TCL win32 console app running against the database
connecting
> in the same way as the web page generator applications.  The console
> app runs every 5 minutes on one of the web servers.

What are you running it with?  WinAT? Or some other cron like utli?  I
have have problems with WinAT.

Quote:

> We also have cold fusion application servers connecting for updates
> and reads, but they use Allaire's ODBC drivers, connection pooling,
> and limit the number of active threads to the database to 5 per
> application server (there are 3 CF servers)

> > >    How are you making your connection to the dB?

> > > Via:
> > >    database dbhnd $datasrcname $userid $password

> The one listed above  ^^^

> > > or
> > >    database dbhnd $connectstr

> > > If it's the first with the datasrcname, do you have it set NOT to
> > > create a temp stored proc for the sql.  I have had problems with
> > > this eating away at temp dB, and killing the server in the
> > > way you spoke of, but not with 7.0.  An idea anyway.

> I'm not sure if it does this or not.
> What would you have to do in code (or configuration) to NOT
> create a temp stored proc? Or conversely, what would you have to do in
> code or configuration files TO create temp stored procedures for the
> SQL?

This setting is changed on your DNS control panel for that Data
source, tho I think I can be may be set at the server level on the Sql
server side.  If you are seeing a Zillion temp stored procs in temp dB
during this time, and they are basicly all the same sp, then this may be
the problem.

Quote:

> > Is your code a cgi app? fast-cgi? or what?

> CGI.  I was told by the developer that FAST-CGI only works with
Apache.
> I have not had time to verify this.

See Fastengines: http://www.fastengines.com/
You could also setup the Xitami web server to serve as your CGI app
server, and use it's LRWP (Long Running Web Process) engine to make your
code persistent, I have a All Tcl package to write LRWP's with (If

easy to convert a CGI prog to be a LRWP, usally anyway :-)  Also the
Xitami web server is very easy to setup, free, fast and runs on most
platforms. See: http://www.xitami.com

Quote:

> > If it's persistent, then are you using some sort of connection
> > pooling to the dB?

> It is not persistent and to the best of my knowledge we are not using
> connection pooling.  Is there some way we can use connection pooling
> from a TCL cgi running under IIS?  Is there a way to convert the
> TCL interpreter into an IIS dll so that it can be persistent?

I have not seen a ISAPI package for Tcl, not sure if there is one, it
would be nice I guess.

Quote:
> >If not, are you closing out your connections after the app is done
> > with it?

> the application wasn't doing this - it was letting the TCL interpreter
> exit clean up and close connections.  We put in explicit disconnects
> and it made no difference.

> > How many conections per app are being made to the dB, i.e. during
> > that page hit?

> Between 2 and 4 connections.

This is something I have had problems with MS-SQL, I have everything
recoded not to use at most 3 connections against the server.  In the 6.x
days this was a problem, but I think 7.0 is oK with it.

Quote:
> >What does the dB log say, any errors?  Logs ok, as in not full?

> No errors that we can identify as affecting this performance problem
> and the logs are not full.

> > Temp dB, how does it stand during this time?

> It gets big. but we have 1.5GB of memory.

How big is temp dB disk space wise?  Again check for the alot of temp
sp's in it if you can.

Quote:

> >Are you doing any big selects/updates/etc... that would lock a lot
> > of rows, and eatup temp dB

> Yes.  and the console app that runs every 5 minutes also does large
> selects.  As far as I know, because of security settings, it is
> doing a read committed every time.

> > and or the logs?  If you are fetching back a lot of data, are you
> > doing:
> >    set ResultSetList [$dbhnd $sqlstr] to get a massive amount of
data,

> We are doing the above (for the most part)

If you are you are fetching back a lot of rows, or big rows, I would try
to fetch them a row at a time.

Quote:

> > or
> >    a
> >    $dbhnd statement dbhnd_stmt $sqlstr/$dbstmthnd fetch combo?

> What is the difference in terms of affect on performance?

In the past, I have passed back thousands of rows and just about halted
the server :-)

Quote:
> > How much memory is in the dB server?

> 1.5 GB

> >Are there any other apps running on the dB  server box?

> Not directly on the box itself, but on the other servers and
connecting
> to the DB.

> > These are all things I have had problems with in the past with
> > m$-sql server, tho none since 7.0 came out (It fixed many sins)

> Is it that you haven't tried the things that used to fail under 6.5
> or that you have, and they don't cause the same problems anymore?

Most of the problems I had went away after the 7.0 install.  I have not
really tried the multi connection problem I had in the past, but I do
have a app now that makes three connections from the same code with no
problems now.

Quote:

> This has all the hallmarks of the application servers
> leaving connections open to the database and not closing them,
> though when the server fails we see between 25 and 30 active
> connections.  It never seems to increase beyond that, and I don't
> even know if that is excessive or reasonable....

Are there really that many connections?  Can you tell?

Quote:
> Any suggestions for things we could try would really help.
> Our former TCL developer is no longer here, and the current
> developer is not a TCL expert, so we are scrambling to fix
> this.

> Thanks,
> -Natalie

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Tue, 11 Mar 2003 03:00:00 GMT  
 TCL, ODBC and SQL Server 7.0 - Database server crashing

Quote:



> > There is a TCL win32 console app running against the database
> connecting
> > in the same way as the web page generator applications.  The console
> > app runs every 5 minutes on one of the web servers.
> What are you running it with?  WinAT? Or some other cron like utli?  I
> have have problems with WinAT.

The cron is Arcana Scheduler 2.1.5, from Arcana Development.

Quote:
> I have not seen a ISAPI package for Tcl, not sure if there is one, it
> would be nice I guess.

Velocigen seems to have one, though I'm not sure if it is any good.

Quote:
> > > Temp dB, how does it stand during this time?
> How big is temp dB disk space wise?  Again check for the alot of temp
> sp's in it if you can.

Observed TEMPDB size when query time was unacceptable (17 secs) was 1.7
MBytes.

Quote:
> > This has all the hallmarks of the application servers
> > leaving connections open to the database and not closing them,
> > though when the server fails we see between 25 and 30 active
> > connections.  It never seems to increase beyond that, and I don't
> > even know if that is excessive or reasonable....

> Are there really that many connections?  Can you tell?

This is what the SQL Server profiler is telling us.  Whether we can
believe it to be true is another thing....

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 15 Mar 2003 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Error 4149 occurred at Fetch Next Record, SQL Server 7.0, and SQL Toolkit 5.0

2. Error 4149 occurred at Fetch Next Record, SQL Server 7.0, and SQL Toolkit 5.0

3. MS SQL Server 7.0

4. SQL Server 7.0 problem

5. SQL Server 7.0 Speed

6. Clipper 5.2 & SQL Server 7.0

7. SQL SERVER 7.0

8. Help: Need to get COBOL alphanumeric (binary) data into SQL Server 7.0

9. Help: Need to get COBOL alphanumeric (binary) data into SQL Server 7.0

10. Microfocus COBOL using SQL Server 7.0

11. Python and MS SQL server 7.0

12. transactions and MS SQL Server 7.0

 

 
Powered by phpBB® Forum Software