469,106 Members | 2,353 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

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;

Jul 20 '05 #1
5 3302
pete (pe**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

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

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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
Jul 20 '05 #3
pete (pe**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
pete (pe**@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.


Jul 20 '05 #5
pete (pe**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Jack Smith | last post: by
reply views Thread by scott_mcarthur2003 | last post: by
11 posts views Thread by Newbie | last post: by
19 posts views Thread by Thue Tuxen Sørensen | last post: by
1 post views Thread by M. Oakley | last post: by
11 posts views Thread by Shane Suebsahakarn | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.