oledbDataAdpater Memory Leak

[Karthikeyan C S]

I have written a Windows Service using VB.NET that reads the mySQL
database and creates an XML for the data. This process is called
inside a timer's (to generate the XML files at specified time
interval) Tick event. I use oledb for connecting to the database.

Now the [Memory usage] for my service in the [Windows Task Manager]
rises each time the timer event is fired and never drops back.

Now after some period of time the system crashes due to memory leak.


When I comment the Fill method code and run the service, the memory
usage is stable.

How can this (type of memory leak) be prevented. Is this due to the
Memory leak problem of oleDBDataAdapter's Fill method.

Why the objects are not Garbage collected after the objects are closed
in the timer_tick event?


I have provided the code that creates memory leak (kind of) in my
service. (i have deleted the lines of code that generates xml files
from the dataset, as this code itself generates memory leak kind of
thing.)

-----------

'code for the Timer1_Tick event

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick

Dim oledbConnection1 As OleDbConnection
Dim svlQuery As String
Dim dsUser As DataSet
Dim oledbDataAdapter1 As OleDbDataAdapter

'oledbConnection1 = InitiateDB()
oledbConnection1 = New OleDbConnection
oledbConnection1.ConnectionString =
"Provider=MySqlProv.3.0;Data Source=DSNTest1;Integrated
Security='';Password=DSNTest1;User
ID=DSNTest1;Location=dbservern01;Extended Properties='OPTION=3'"
Try
oledbConnection1.Open()
Catch myExp As Exception
Exit Sub
End Try

svlQuery = "select * from SampleTable"

dsUser = New DataSet
Try
oledbDataAdapter1 = New OleDbDataAdapter(svlQuery,
oledbConnection1)
oledbDataAdapter1.Fill(dsUser)
Catch myExp As Exception
oledbDataAdapter1 = Nothing
oledbConnection1 = Nothing
Exit Sub
End Try

dsUser.Clear()
dsUser.Dispose()

oledbDataAdapter1.Dispose()

oledbConnection1.Close()
oledbConnection1.Dispose()

End Sub

 

[Marina]

If during data retrieval something goes wrong, you never close your
connection, since you just set the object to nothing. This means you have
an open connection lying around you never closed.

I would restructure this into one try/catch with a finally block. The
finally block would close the connection.
You should not need to dispose the connection nor any of the other objects.
The connection is the only one with unmanaged resources, and a call to Close
should release those.

Now, it is very well possible there is some sort of memory leak. To avoid
it, make these object class level members. Then, just call Fill when
necessary (you don't need to open the connection even, Fill will open a
closed connection and then close it). This will avoid you recreating
objects all the time. There will just be one set of them.

I have written a Windows Service using VB.NET that reads the mySQL
database and creates an XML for the data. This process is called
inside a timer's (to generate the XML files at specified time
interval) Tick event. I use oledb for connecting to the database.

Now the [Memory usage] for my service in the [Windows Task Manager]
rises each time the timer event is fired and never drops back.

Now after some period of time the system crashes due to memory leak.


When I comment the Fill method code and run the service, the memory
usage is stable.

How can this (type of memory leak) be prevented. Is this due to the
Memory leak problem of oleDBDataAdapter's Fill method.

Why the objects are not Garbage collected after the objects are closed
in the timer_tick event?


I have provided the code that creates memory leak (kind of) in my
service. (i have deleted the lines of code that generates xml files
from the dataset, as this code itself generates memory leak kind of
thing.)

-----------

'code for the Timer1_Tick event

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Timer1.Tick

Dim oledbConnection1 As OleDbConnection
Dim svlQuery As String
Dim dsUser As DataSet
Dim oledbDataAdapter1 As OleDbDataAdapter

'oledbConnection1 = InitiateDB()
oledbConnection1 = New OleDbConnection
oledbConnection1.ConnectionString =
"Provider=MySqlProv.3.0;Data Source=DSNTest1;Integrated
Security='';Password=DSNTest1;User
ID=DSNTest1;Location=dbservern01;Extended Properties='OPTION=3'"
Try
oledbConnection1.Open()
Catch myExp As Exception
Exit Sub
End Try

svlQuery = "select * from SampleTable"

dsUser = New DataSet
Try
oledbDataAdapter1 = New OleDbDataAdapter(svlQuery,
oledbConnection1)
oledbDataAdapter1.Fill(dsUser)
Catch myExp As Exception
oledbDataAdapter1 = Nothing
oledbConnection1 = Nothing
Exit Sub
End Try

dsUser.Clear()
dsUser.Dispose()

oledbDataAdapter1.Dispose()

oledbConnection1.Close()
oledbConnection1.Dispose()

End Sub

-----------

Regards

Karthikeyan C S