MS SQL Server DTS - Tracking down a memory leak

Author

Tracking down a memory leak

Griff

2005-09-26, 7:23 am

We have a DTS package (VBScript) that contains an iterative loop. Within
this loop, it will instantiate a DLL (VB6), call a method on this DLL and
then set the instance to nothing.

Within the DLL I have studiously tidied up all the in-memory objects;
disconnected recordsets are closed and set to nothing, ADO connections are
closed and set to nothing, arrays are erased etc, etc.

The problem is that, when dealing with loop iterations > 5, the server ends
up grinding to a halt. If one follows this using Task Manager, one can see
the memory consumed gradually ramp up until it presumably starts to use the
swap file.

I therefore need to know how this memory is being leaked. Is it my DLL, is
it the DTS package, is it SQLServer (SQLServer and the database in question
all reside on the same server)? Of course, the DLL and DTS all run in
SQLServer's process space, so Task Manager can only report that it's
SQLServer that's taking all the memory - not much help to me.

So what would be the best way to tackle this? Any suggestions would be most
appreciated.

Thanks in advance

Griff

J French

2005-09-26, 9:23 am

On Mon, 26 Sep 2005 11:28:04 +0100, "Griff" <Howling@The.Moon> wrote:

>We have a DTS package (VBScript) that contains an iterative loop. Within
>this loop, it will instantiate a DLL (VB6), call a method on this DLL and
>then set the instance to nothing.

Try keeping the DLL alive
ie: not set it to Nothing and re-use the first instance

If nothing else that will show whether the 'leak' is caused by the DLL
not releasing resources.

It is also a good ideal to monitor the Initialize and Terminate Events
of just about everything ....

Griff

2005-09-26, 11:23 am

I have attempted to monitor the memory (using Task Manager) whilst stepping
through the VB6 code in the IDE.

It appears that sometimes the memory allocated to a disconnected ADO
recordset is not being released.

I declare the recordset using:
Dim oRS as adodb.recordset

I set the recordset by setting it to a function that returns a recordset
set oRS = oDA.getDataA

Let us say that the memory in this step goes from 25 MB to 30 MB

I then close the recordset
oRS.close
and the memory decreases slightly

And then set it to nothing
Set oRS = nothing

and the memory stays the same, despite apparently trashing the recordset.
There are no other objects pointing to this in memory....

Later on, I use the pointer to now hold another set of data

set oRS = oDA.getDataB

This set of data is far smaller than the original one and guess what, the
memory consumed by the DLL drops from 30 MB to (say) 26 MB. This implies to
me that although I'd closed and destroyed the recordset, it still remained
in memory. As mentioned above, there were NO other objects referencing this
recordset (which would have kept it alive).

What I can't tell is whether this behaviour is happening in my compiled DLL
or whether it's just because I'm running it in the VB6 IDE in debug mode.

By the way, am using the latest version of MDAC/ADO & VB6 service packs.

Griff

Bonny Gijzen

2005-09-27, 7:23 am

Hi,

Windows isnt neceserally immediately freeing the memory (for performance
issues).
It keeps it cached sort to say for your app to use it later on.
So then mem seems to stay the same until something is done with the memory
handler, i.e. requesting new memory,
or explicitly saying that the free mem should be cleared.

You could use following code to make Windows really free your mem:

MainHandle : THandle;

MainHandle := OpenProcess(PROCESS_
ALL_ACCESS, false, GetCurrentProcessID)
;
SetProcessWorkingSet
Size(MainHandle, DWORD(-1),DWORD(-1) );
CloseHandle(MainHand
le);

--
Many regards,

Bonny Gijzen
TB Systems
Holland
"Griff" <Howling@The.Moon> wrote in message
news:uoPoIqqwFHA.908@tk2msftngp13.phx.gbl...
> I have attempted to monitor the memory (using Task Manager) whilst
stepping
> through the VB6 code in the IDE.
>
> It appears that sometimes the memory allocated to a disconnected ADO
> recordset is not being released.
>
> I declare the recordset using:
> Dim oRS as adodb.recordset
>
> I set the recordset by setting it to a function that returns a recordset
> set oRS = oDA.getDataA
>
> Let us say that the memory in this step goes from 25 MB to 30 MB
>
> I then close the recordset
> oRS.close
> and the memory decreases slightly
>
> And then set it to nothing
> Set oRS = nothing
>
> and the memory stays the same, despite apparently trashing the recordset.
> There are no other objects pointing to this in memory....
>
> Later on, I use the pointer to now hold another set of data
>
> set oRS = oDA.getDataB
>
> This set of data is far smaller than the original one and guess what, the
> memory consumed by the DLL drops from 30 MB to (say) 26 MB. This implies
to
> me that although I'd closed and destroyed the recordset, it still remained
> in memory. As mentioned above, there were NO other objects referencing
this
> recordset (which would have kept it alive).
>
> What I can't tell is whether this behaviour is happening in my compiled
DLL
> or whether it's just because I'm running it in the VB6 IDE in debug mode.
>
> By the way, am using the latest version of MDAC/ADO & VB6 service packs.
>
> Griff
>
>
>
>
>

Tony Proctor

2005-09-29, 1:23 pm

As a good starting point, I'd make sure your DLL project (and any other DLLs
it uses) has the 'Unattended execution' and 'Retain in Memory' options set

Tony Proctor

"Griff" <Howling@The.Moon> wrote in message
news:ORkK8TowFHA.664@tk2msftngp13.phx.gbl...
> We have a DTS package (VBScript) that contains an iterative loop. Within
> this loop, it will instantiate a DLL (VB6), call a method on this DLL and
> then set the instance to nothing.
>
> Within the DLL I have studiously tidied up all the in-memory objects;
> disconnected recordsets are closed and set to nothing, ADO connections are
> closed and set to nothing, arrays are erased etc, etc.
>
> The problem is that, when dealing with loop iterations > 5, the server
ends
> up grinding to a halt. If one follows this using Task Manager, one can
see
> the memory consumed gradually ramp up until it presumably starts to use
the
> swap file.
>
> I therefore need to know how this memory is being leaked. Is it my DLL,
is
> it the DTS package, is it SQLServer (SQLServer and the database in
question
> all reside on the same server)? Of course, the DLL and DTS all run in
> SQLServer's process space, so Task Manager can only report that it's
> SQLServer that's taking all the memory - not much help to me.
>
> So what would be the best way to tackle this? Any suggestions would be
most
> appreciated.
>
> Thanks in advance
>
> Griff
>
>

Sponsored Links