Memory Leak. SQL Server sp3a, VB and MDAC 2.8

Can anyone give me some sugguestions here.
Connection is declared at the start of the application
Set rsFZReport.ActiveConnection = conn

Then a function repeatly opens recordsets like this -

rsFZReport.Open sRS, , adOpenStatic, adLockReadOnly, adCmdText
..

..
reporting code etc
...

If rsFZReport.State > 0 Then
rsFZReport.Close
Set rsFZReport= Nothing
endif

Using VB Watch debugger, the close and set nothing do NOT release any
memory. It just keeps increasing. The application connects to multiple
databases and many tables, so its uses about 10Mb/minute.
(I don't think its of relavance but Form.show uses memory but form.unload
doesn't release any back)
I've installed SP3a and MDAC 2.8 to no avail. Anyone have any ideas or know
what I can do?

msado27.tlb 2.80.1022.0
MSSQL Server SP3a (3 has a memory leak apparently)
VB6, SP6
Sqlsrv32.dll caused a leak on NT4 SP6, but upgrading it on my win2k SP4
server/workstation (they are both and the same) to this
Sqlsrv32.dll 2000.85.1022.0
doesn't help.
Provider=SQLOLEDB;Data Source=theServer;Initial Catalog=theDB;User
ID=uid;Password=thepwd;OPTION=3;connect timeout=240;pete (pete@.madpete.freeserve.co.uk) writes:
> Can anyone give me some sugguestions here.
> Connection is declared at the start of the application
> Set rsFZReport.ActiveConnection = conn
> Then a function repeatly opens recordsets like this -
> rsFZReport.Open sRS, , adOpenStatic, adLockReadOnly, adCmdText
> .
> .
> reporting code etc
> ..
> If rsFZReport.State > 0 Then
> rsFZReport.Close
> Set rsFZReport= Nothing
> endif
>
> Using VB Watch debugger, the close and set nothing do NOT release any
> memory. It just keeps increasing. The application connects to multiple
> databases and many tables, so its uses about 10Mb/minute.

I don't have any experience of tracing memory leaks in Visual Basic,
but I suspect that there is a wee bit too little of information to say
anything.

> MSSQL Server SP3a (3 has a memory leak apparently)

Yes, there was a memory leak in ODBC in SP3, but you are using SQLOLEDB,
so that you should not bother you.

> VB6, SP6
> Sqlsrv32.dll caused a leak on NT4 SP6, but upgrading it on my win2k SP4
> server/workstation (they are both and the same) to this
> Sqlsrv32.dll 2000.85.1022.0
> doesn't help.

Sqlsrv32.dll is ODBC, so again it should not bother you.

> Provider=SQLOLEDB;Data Source=theServer;Initial Catalog=theDB;User
> ID=uid;Password=thepwd;OPTION=3;connect timeout=240;

OPTION=3 strikes me as unusual. What does it do?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||">
> OPTION=3 strikes me as unusual. What does it do?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

It was recommended by someone to overcome a problem with varchars not
returning variable length fields. The records are large and the were being
cut off., (only the first few thousand characters returned for a field)
fields were not being returned complete. I havn't actually found any
documentation on this, but it seemed to fix the problem|||pete (pete@.madpete.freeserve.co.uk) writes:
>> OPTION=3 strikes me as unusual. What does it do?
>>
> It was recommended by someone to overcome a problem with varchars not
> returning variable length fields. The records are large and the were being
> cut off., (only the first few thousand characters returned for a field)
> fields were not being returned complete. I havn't actually found any
> documentation on this, but it seemed to fix the problem

I can't find this in the docs either. And I have never heard any problems
with varchar(8000) being truncated.

What happens if you take it out with regards to the memory leak?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95368D79BCD9AYazorman@.127.0.0.1...
> pete (pete@.madpete.freeserve.co.uk) writes:
> >> OPTION=3 strikes me as unusual. What does it do?
> >>
> > It was recommended by someone to overcome a problem with varchars not
> > returning variable length fields. The records are large and the were
being
> > cut off., (only the first few thousand characters returned for a field)
> > fields were not being returned complete. I havn't actually found any
> > documentation on this, but it seemed to fix the problem
> I can't find this in the docs either. And I have never heard any problems
> with varchar(8000) being truncated.
> What happens if you take it out with regards to the memory leak

I didn't make a differnce, but this was the problem

Global rsFZReport as new ADODB.recordset
Which is dumb.
And so is OPTION=3 . I can't find the explanation for it now. I've searched
the MSDN and the web. It might simply be wrong, its used for MySQL though.|||pete (pete@.madpete.freeserve.co.uk) writes:
> I didn't make a differnce, but this was the problem
> Global rsFZReport as new ADODB.recordset
> Which is dumb.

We all do dumb things from time to time.

> And so is OPTION=3 . I can't find the explanation for it now. I've
> searched the MSDN and the web. It might simply be wrong, its used for
> MySQL though.

So I would conclude that Option is specific to the MySQL provider.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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