Problems with MSSQL SERVER 2000 after few days of normal work

[spavkov]

Hi all..

i have a very strange problem...

In my company i have a strong HP ProLiant 3ghz server with 1GB of ram.

Windows 2000 Server is installed and a MSSQL 2000 also...
MDAC version 2.8.

i have about 20 client computers that use about 15 different databases on this server...

before all worked well..

recently, it happened that some of users called me saying that they are having problems working with db applications.

it turned out that they can open their db apps (written in Delphi 7 using ADO for database access) and browse thru the data, but cannot insert any new records in tables, or change any other data in tables...

if they try, programs just freeze and dont respond...

i started SP_MONITOR stored procedure and received info that
CONNECTIONS > 30000

this is cummulative value, i know this, but it looked strange....

so i restarted the SQL server service and after this all was good again...

but after few days i got the same problem...
after looking at sp_monitor info i could (again) see that
CONNECTIONS > 30000

is this related???

anyone having problems like this...??

please help, i cannot restart my SQL server every few days, there must be a better solution...

???

 

[macleod1021]

It sounds like the program is not explicitly calling the .close() method of their ADO connection. A way you can test this is to reboot the server and have ONE person connect. Have them go through a few transactions and see if the connections start accumulating. A responsible app will close each connection that it opens.

I don't know a lot about Delphi so if this is what's happening, then you need to talk to the Vendor about fixing this. There may be an option to drop connections after a certain amount of inactivity, but that's just a band-aid for a serious leak in the app.

 

[spavkov]

Thank alot for quick answer...

this forum (and people involved) is really something...

i will see if this helps and let you know....

Regards
Slobo

 

[spavkov]

Ok, i checked, and it looks this was not a problem...

here is my check:

i ran this query :

exec sp_who2
and it showed 98 connections active.

i started on app, ran some db tasks, and rad exex sp_who2
and it showed 99 connections active.

i stopped the app and sp_who2 showed 98 connections...

so this should mean that closing connections is not an issue here i think... ?

or maybe im wrong?

is there a way to check this the other way?

what should i do when the problem happens to find out the reason?

last time it happened i checked the server and all looked normal.( cpu, memory... all ok)

it looked just like something has a lock on all the databases so nothing can write to tables, but reading is possible (a little slow, but possible)..

ok, that were my clues, but im clueless

any ideas/sugesstions ?

Slobo

 

[vongrunt]

How client computers modify data? Via stored procedures, client-side (ADO or dynamic SQL) or both? With or without transactions?



------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[spavkov]

Client computers modify data thru applications written in Delphi 7 that use Delphi generic VCL ADO components:

1. AdoConnection
2. AdoQuery
3. AdoTable

some of the applications use transactions to modify the tables...
but mostly only normal queries are used (select, update, insert).

there are no stored procedures...

there are a number of views in the databases...

also there are some triggers (not many of them).

do you think that there are some transactions that are sometimes stuck and this is causing server to freeze ???

this looks like a possible solution to me...

but how to determine that?

there are at least 30 users/computers running multiple applications at the same time...

i hope this answers your questions...