Sql bulk copy memory issue

Question

We are using SqlBulk Copy class in C#. To insert Bulk data in sql. We have a table with 10 million records in it.

We are inserting data in a batch of 10,000 in a loop

We are facing physical memory issue.The memory gets increased and do not get reduced.

Below is our code . How we can release memory when sql bulk copy is used or is there any another way to do bulk insert.

using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(SQlConn,SqlBulkCopyOptions.TableLock,null))
{
    //bulkCopy = new System.Data.SqlClient.SqlBulkCopy(SQlConn);
    bulkCopy.DestinationTableName = DestinationTable;
    bulkCopy.BulkCopyTimeout = 0;
    bulkCopy.BatchSize = dt1.Rows.Count;
    Logger.Log("DATATABLE FINAL :" + dt1.Rows.Count.ToString(), Logger.LogType.Info);
    if (SQlConn.State == ConnectionState.Closed || SQlConn.State == ConnectionState.Broken)
        SQlConn.Open();
    bulkCopy.WriteToServer(dt1); //DataTable
    SQlConn.Close();
    SQlConn.Dispose();
    bulkCopy.Close();
    if (bulkCopy != null)
    {
        ((IDisposable)bulkCopy).Dispose();
    }                        
}

Here updating the complete code.

try
        {

            using (SqlConnection SQlConn = new SqlConnection(Common.SQLConnectionString))
            {


                DataTable dt1 = FillEmptyDateFields(dtDestination);

                //SqlTableCreator ObjTbl = new SqlTableCreator(SQlConn);

                //ObjTbl.DestinationTableName = DestinationTable;
                using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(SQlConn,SqlBulkCopyOptions.TableLock,null))
                {

                    //bulkCopy = new System.Data.SqlClient.SqlBulkCopy(SQlConn);
                    bulkCopy.DestinationTableName = DestinationTable;
                    bulkCopy.BulkCopyTimeout = 0;
                    bulkCopy.BatchSize = dt1.Rows.Count;
                    Logger.Log("DATATABLE FINAL :" + dt1.Rows.Count.ToString(), Logger.LogType.Info);
                    if (SQlConn.State == ConnectionState.Closed || SQlConn.State == ConnectionState.Broken)
                        SQlConn.Open();
                    bulkCopy.WriteToServer(dt1);
                    SQlConn.Close();
                    SQlConn.Dispose();
                    bulkCopy.Close();
                    if (bulkCopy != null)
                    {
                        ((IDisposable)bulkCopy).Dispose();
                    }                        
                }

            }

            dtDestination.Dispose();
            System.GC.Collect();
            dtDestination = null;
        }
        catch (Exception ex)
        {
            Logger.Log(ex, Logger.LogType.Error);
            throw ex;

        }

Are you using one big datatable, or feeding in the same datatable everytime for each 10,000 rows or creating a new one for each block of 10,000 rows? You should try calling dt1.Clear each time if it's the third. The SqlBulkCopy is unlikely to be causing the issue here - it's likely to be the datatable holding the data. Note there is no need to call bulkCopy.Dispose() - this will be done when you exit the `using` block. — dash, Oct 10 '12 at 12:21
What is the source of the data, there is a lot of relevant code missing here. — Mike Perrenoud, Oct 10 '12 at 12:22
@Dash.. Thanks for your quick reply.We are actually importing Universe DB Table To Sql. Yes We are feeding new table every time. — sunshine, Oct 10 '12 at 12:24
I wouldn't do it any way other than the way @MarcGravell has suggested :-) — dash, Oct 10 '12 at 12:25
@user1728812 Note that calling `Dispose()` on a DataTable doesn't actually do what you may think it does - http://stackoverflow.com/questions/913228/should-i-dispose-dataset-and-datatable — dash, Oct 10 '12 at 15:13

score 9 · Answer 1 · answered Oct 10 '12 at 12:24

The key question here would be: what is dt1, where did it come from, and how have you released it? DataTable is actually quite tricky to clean out, and frankly I wouldn't normally recommend a DataTable source here. However, if you must use DataTable, then make sure and use a completely separate DataSet / DataTable per iteration, and release the old one so it can recycled.

More efficient, however, is to use WriteToServer(IDataReader) - this allows you to handle rows in a streaming fashion. If you are copying between two SQL systems, you could even just use ExecuteReader() on a separate command / connection, but IDataReader is pretty simple, and you can write a basic IDataReader for most sources (or find libraries that do so, for example CsvReader for handling delimited files such as csv/tsv).

Dt1 is nothing but the datatable we are inserting. No we are disposing dt1. Below is our whole code — sunshine, Oct 10 '12 at 12:35

score 1 · Answer 2 · answered Oct 10 '12 at 12:27

1

I guess the issue is with this line:

bulkCopy.BatchSize = dt1.Rows.Count;

The BatchSize property determines how many rows are inserted in a single internal transaction. The row size here is potentially unbounded.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.batchsize.aspx

Try set it to a small and fixed number should solve the problem:

bulkCopy.BatchSize = 1000;

It's up to you to decide the optimal batch size here.

answered Oct 10 '12 at 12:27

xing

447
2
6

@Kamil Krasinski : We are disposing dt1 datatable object. Do we need to release it also ? – sunshine Oct 10 '12 at 12:36
Please let us know the max limit we can give to BatchSize . Can we give upto 1 lakh. ? – sunshine Oct 10 '12 at 12:38
You may want to set it to null to release memory. I don't think dispose is useful because DataTable doesn't contain any unmanaged resource. – xing Oct 10 '12 at 12:41
There isn't a hard limit on BatchSize. You may want to test several different numbers to see how it affect performance. – xing Oct 10 '12 at 12:42

Sql bulk copy memory issue

2 Answers2

Linked