SQL Server runs out of resources or is it my client application. Handle count = 96k

[B Moor]

SServer PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM
SQL Server 2000 v 2000.8.00.76 (sp3)
MS Office 2k3
MSJet ms04-014 (latest ost sp8)
MDAC v2.8 RTM
ADO 2.1

vb6.exe / ADO 2.0

I think this is a SQL Server/ADO problem as I have 2 applications with
same problem.
My access database uses a timer based function to insert records into
SQL Server using ADO and stored procedures. Access also uses DAO ,
Jet/ odbc to linked tables on SQL Server for many other tasks/forms.
All is well when Access 1st run but after a few hours or so the Access
app grinds to a halt.
Upon checking the task manager the mem usuage upto 160MB and handle
count upto 86,000 ! (cpu process % is low).

After the "Access Fail" if I stop/start access only, performance is
not returned, I have to stop/start SQL Server.It would seem that all
connections from this PC to SQL server are badly affected, it is not
tied to the client application that had the problem.

As I could not work out where the problem was I took the Access
functionality into a VB6 app, using ADO 2.0, thinking this should
simplify matters with Jet and ODBC out of the way.

I now have the same problem with the number of handles increasing with
every new timer based function.

\* code snippet example *\
If Not OpenConnection Then 'we have not been able to open a
connection to SQL server
Call procLog("Connection failed to SQL server")
Exit Function
End If

'gVar.cnnSQL is my public ADODB.Connection
Set cmdSQL = New ADODB.Command
With cmdSQL
.ActiveConnection = gVar.cnnSQL
.CommandText = "MyDB.dbo.insert_tblMyData"
.CommandType = adCmdStoredProc
.Execute RecordsAffected:=lngRecs, _
Parameters:=Array(lngID, dtDate,intCategory,
strNationality,strNotes,strName)
End With
\* code snippet *\
** After the "Access Fail" if I look at one of my clients, running the
same Access app on another PC, it seems normally responsive when using
one my bound forms to browse the data from same SQL Server **

Any ideas anyone ?

[B Moor]

I have now determined that if I run my VB application on a separate PC
I do not have a problem, so therefore I believe the problem is in the
SBS Server and MDAC but I dont know how to fix this as we have the
latest versions.

I have re-created this on a test SBS 2003 with exactly the same
symptoms of rapidly escalating handle counts for the VB.exe, so this
rules out some quirk on the site SBS 2003.

Should I look to uninstalling MDAC and putting am earlier version on ?

[Erland Sommarskog]

B Moor (mo******@yahoo.co.uk) writes:

My access database uses a timer based function to insert records into
SQL Server using ADO and stored procedures. Access also uses DAO ,
Jet/ odbc to linked tables on SQL Server for many other tasks/forms.
All is well when Access 1st run but after a few hours or so the Access
app grinds to a halt.
Upon checking the task manager the mem usuage upto 160MB and handle
count upto 86,000 ! (cpu process % is low).

What process is this? The VB app, or SQL Server? (And I have to admit that
I don't know what the Handle count tells us.)

Since you don't seem to get the problem with you run the application on
another machine, it may be an issue with shared memory, which is the default
connection model when application and SQL Server is on the same machine.
You could test this by turning off shared memory in the Client Network
Utility, or force TCP/IP in the connection string.

Really what is going on, I don't know, but I notice that in your snippet
you are not saying things like:

Set cmdSQL = Nothing
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[B Moor]

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

B Moor (mo******@yahoo.co.uk) writes:

My access database uses a timer based function to insert records into
SQL Server using ADO and stored procedures. Access also uses DAO ,
Jet/ odbc to linked tables on SQL Server for many other tasks/forms.
All is well when Access 1st run but after a few hours or so the Access
app grinds to a halt.
Upon checking the task manager the mem usuage upto 160MB and handle
count upto 86,000 ! (cpu process % is low).
What process is this? The VB app, or SQL Server? (And I have to admit that
I don't know what the Handle count tells us.)

The process is the VB app.exe , and before when using MS Access it was
the MSAccess.exe.
Since you don't seem to get the problem with you run the application on
another machine, it may be an issue with shared memory, which is the default
connection model when application and SQL Server is on the same machine.
You could test this by turning off shared memory in the Client Network
Utility, or force TCP/IP in the connection string.

Brilliant !
I think that was it, I only checked this thread tonight (Sunday) and
quickly tried this and it works, will leave overnight to see then.
I had not appreciated significance of the shared memory checkbox; it
certainly made no difference on XP (I installed SQL server on Xp for
development purposes) but does on SBS.

Thanks again.