Memory Leak problem using SQLBulkCopy class

[SteveB]

Hi All,

Using Windows Vista Ultimate 64 bits with MS SQL 2005 SP2.

When ever I am trying to import big files like a CSV format with 24 mil records I am getting a memory leak
which will consume all available memory available. When I close Visual Studio 2008, still the memory will not still
be released into the OS.

I need basically to reboot in order to claim back the memory. Here is the driver that I am using for the CSV
format. Below is some sample code.

Any idea how to resolve the memory leak problem?

Dim connbuilderOLEDB As New OleDbConnectionStringBuilder

connbuilderOLEDB("Provider") = "Microsoft.Jet.OLEDB.4.0"

connbuilderOLEDB("Extended Properties") = "text;HDR=Yes;FMT=Delimited(',')"

connbuilderOLEDB("Data Source") = Path.GetDirectoryName(DBFileName)

ConnStrOleDb = connbuilderOLEDB.ConnectionString


Using connOleDb As New OleDbConnection(ConnStrOleDb)

connOleDb.Open()

Dim dbcmd As New OleDbCommand(String.Format("select * from {0}", TableName), connOleDb)

dbcmd.CommandTimeout = SQL_TIMEOUT

bulkcopy.WriteToServer(dbcmd.ExecuteReader())

connOleDb.Close()

connOleDb.Dispose()

End Using



Thanks,



Steve

 

[bruce barker]

well you fail to dispose the command, reader and bulkcopy. the jet engine
is probably running out of process and may be the trouble. (not sure why
you'd use it anyway)

-- bruce (sqlwork.com)

 

[SteveB]

Hi Bruce,

So what other options do I have instead of the jet engine? So I can import
the CSV file.

Thanks,

Steve.

 

[bruce barker]

well the native sqlclient library has builtin bcp support and can read
csv files directly. you still must learn to call dispose properly.


-- bruce (sqlwork.com)

 

[SteveB]

Hi Bruce,

Thanks for your reply.
The BCP seems to be a better direction. I am not expert in .net yet so please let me know what's wrong with the Dispose()

Using connOleDb As New OleDbConnection(ConnStrOleDb)
connOleDb.Open()

Dim dbcmd As New OleDbCommand(String.Format("select * from {0}", TableName), connOleDb)

dbcmd.CommandTimeout = SQL_TIMEOUT

bulkcopy.WriteToServer(dbcmd.ExecuteReader())

connOleDb.Close()

connOleDb.Dispose()

End Using


Thanks,

Steve