none
SqlBulkCopy fails randomly RRS feed

  • Question

  • Hi

    I am using SqlBulkCopy to import alot of data in my DB. During this process, I noticed very high memory usage (2.5GB) by sqlserver process on my development server. At this time, when I run my application, randomly I get following error

    L'index était hors limites. Il ne doit pas être négatif et doit être inférieur à la taille de la collection.
    Nom du paramètre : index at    à System.Collections.ArrayList.get_Item(Int32 index)
       à System.Data.SqlClient.BulkCopySimpleResultSet.get_Item(Int32 idx)
       à System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
       à System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
       à System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
       à System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)

    Whereas the same request works fine if the memory usage of sql server process is not much. I am developing with .Net 3.5 + Sql Server 2008 R2

    Can you please tell me how I can approach this problem ?

    Thanks, Regards

    Jyotsna

    Tuesday, December 10, 2013 10:01 AM

All replies

  • One of the causes could be, it is timing out and for some reason it is propagated as Index Out of Range exception.

    Try increasing BulkCopyTimeout to unlimited time as below,

    bulkCopy.BulkCopyTimeout = 0;


    Lingaraj Mishra

    Tuesday, December 10, 2013 1:18 PM
  • Hello,

    As far as I know, the error “Index was out of range. Must be non-negative and less than the size of the collection.” usually occurs when we try to access an element of a collection which is outside of its bounds (e.g. element 10 of a 9 element array).

    If we perform some sort of sanity check (e.g. if index < count) on the index which is passed in to make sure that it is not greater than the last indexable element, we shouldn't get the exception.

    It is appreciate that if you share the codes which throws the error message so that we can know how the business logic go in the program and help you better.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred BaoModerator Wednesday, December 18, 2013 9:39 AM
    • Unmarked as answer by Jyotsna Tuesday, December 24, 2013 7:48 AM
    Wednesday, December 11, 2013 3:05 AM
    Moderator
  • Will try this and let you know. Currently, I have set the timeout to 2000 . One more observation is that it fails within 1 second - like after 600 ms sometimes. So I doubt it is due to timeout.
    • Edited by Jyotsna Tuesday, December 24, 2013 7:51 AM More information in response to a suggestion
    Tuesday, December 24, 2013 7:49 AM
  • Hi,

    >> So I doubt it is due to timeout.

    For this, you can have a try to use advice provided by Lingaraj to set bulkCopy.BulkCopyTimeout = 0 so that it will have enough time to execute.

    Regadrs.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 25, 2013 2:11 AM
    Moderator
  • Hi

    I tried the suggestion to set 0 timeout but the problem remains as is.

    As I said, I do not think it is a timeout issue as the error occurs within 600 ms.


    Friday, December 27, 2013 5:38 AM
  • Hello,

    Sorry for misunderstanding you.

    My suggestion is to have a try to run the program in debug model and add a try catch block and add a breakpoint on catch block, when the error occurs, we can check whether the data has some difference.

    In General about this error: This happens when you refer to an "item" from "itemcollection" when the collection itself has no values.

    And when "get_Item" is called but there is nothing to get. (i.e. the collection is empty), it will throw such an error.

    Hope this helps!

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 27, 2013 9:53 AM
    Moderator
  • Hi Fred

    Thanks for your reply. But as I said this is a random error. I tried debugging it several times , but in vain because it always executes successfully when I do that in visual studio.

    Another point to note is that if I execute the same code with same input at a stage where sqlserver process memory consumption is not so high in task manager, then the same request executes successfully. 

    So I am pretty sure there is nothing wrong with the code as it executes at 1 time and not at another. At this point of time, even a confirmation that this is only SQLEXPRESS problem will help me too as the staging environment will be on enterprise.

    Thanks, Regards

    Jyotsna


    Friday, December 27, 2013 10:13 AM
  • Hello Jyotsna,

    I notice that you will import a lot of data to DB and it cause the very high memory usage (2.5GB), this issue may be caused by the high memory usage. So you can have a try to reduce the amount of data which is being inserting into DB one time to see whether it will throw error again.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, December 30, 2013 9:49 AM
    Moderator
  • Hey Fred

    I cannot control the size of input. And out of 200 such bulk loads, it fails for about 10. Meaning that other 190 still complete successfully even at high memory usage. 

    Is there any way someone from MS can confirm if this is only SQLEXPRESS specific problem ?

    Thanks

    Jyotsna


    • Edited by Jyotsna Monday, December 30, 2013 11:01 AM
    Monday, December 30, 2013 11:01 AM
  • Hello Jyotsna,

    The error says "The index is out of range". That means you were trying to index an object with a value that was not valid. If you have two books, and I ask you to give me your third book, you will look at me funny. This is the computer looking at you funny. You said - "create a collection". So it did. But initially the collection is empty: not only is there nothing in it - it has no space to hold anything. "It has no hands".

    It should not be a SQLEXPRESS specific problem.

    Cound you please share how you use the SqlbulkCopy?

    Regards.


    Tuesday, December 31, 2013 8:08 AM
  • Hi AlpacaYou

    Thanks for writing. Yes I completely understand the meaning of "The index is out of range". But all I am doing is calling writeToServer

    System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)

    I do not know where the collection is created and how it is being used. And as I said, the same input with same code works some other time - even with high CPU usage.

    /// <summary>
    /// Function to perform sql bulk copy from csv file
    /// </summary>
    /// <param name="_csvFilePath">Absolute path of csv file to bulk load</param>
    /// <param name="_fieldSeparator">Field separator used in csv file</param>
            private void bulkLoadAccomodations(string _csvFilePath, char _fieldSeparator, bool _isactivePresent)
            {
                this.logCtrl.logTransition(LogPhase.RQ_TRANSMITTED, TransistionDirection.Out, "CSV bulk load in se_accomodation_import");

                int nTimeOut = int.Parse(System.Configuration.ConfigurationManager.AppSettings["upload_sp_timeout_seconds"]);

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(getConnectionString()))
                {
                    getAccomodationColumnMappings(bulkCopy, _isactivePresent);
                    bulkCopy.BulkCopyTimeout = nTimeOut;
                    var reader = new CSVReader(_csvFilePath, _fieldSeparator);
                    bulkCopy.WriteToServer(reader);
                    reader.Close();
                }
            }


    This is how my bulk load function looks like. 

    Tuesday, December 31, 2013 10:02 AM
  • Hello,

    Thanks for sharing the codes, however, we cannot reproduce the error, if this is an urgent issue, you can connect:

    http://support.microsoft.com/

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, January 1, 2014 5:33 AM
    Moderator
  • Hi Jyotsna,

    The SqlBulkCopy uses DataSets to send the data to the SQL Server, so first be sure that you don't have any other thread or event that try to manipulate the same DataSet while the SqlBulkCopy is running.

    The high usage of memory by the SQL Process reveals that this problem is not in you app, but in your database. Check for indexes, triggers, locks, when this happens. If you can get help from a DBA to identify the reason for the memory increase.

    You should also configure the SQL server to avoid that it reach critical levels of memory and cpu usage.

    If you need more help just let me know.

    Thanks

    Tuesday, January 21, 2014 10:18 PM
  • Hello Carlos

    Thanks for your confirmation and response.

    I have checked code and confirm that there is only 1 thread at a time working with bulkload object. Is this enough ?

    I have looked at the database and there are no indices,triggers on the tables I am bulk loading to. Afterwards, we call a stored procedure to process and distribute the bulkloaded data in appropriate tables. My guess is that since this processing SP works with many records at a time, it reserves space in temdb and increases transaction log as well with many update/inserts. This in my opinion is causing the memory increase for Sql server process. But I am not an expert of SQL server. I will convey your suggestions to DBA.

    Thanks again

    Jyotsna

    Wednesday, January 22, 2014 1:12 AM