0

Scenario :

I have a asp.net web service that uses SQL Server 2008. It is supposed to perform some long running DB updates and it involves bulk inserting records to 3 tables in 1 request. There are approximately 220 such requests.

Flow for the a request execution is

read file having data

bulk copy into table1
call sp to process data in table1

bulk copy into table2
call sp to process data in table2

bulk copy into table3
call sp to process data in table3

Out of these 220 requests almost 1/3rd of them fail while trying to bulk load data in either table 1 or table2 with two kinds of exceptions

  1. column mapping does not match in source or destination
  2. argument out of range exception

table3 bulk load never fails if it executes

This is happening on test server. When I get the same file from test server for which I see error and debug it on my machine, everything works fine.

Also a random column mapping error does not make sense because the structure of data in file always remains the same and it does not generate error when executing from development machine.

Another observation is that it is making SQL Server memory consumption very high, so much so that eventually it starts throwing OutOfMemory exception

I have no clue where to start to narrow down the problem. Please advise.

EDIT: I tried setting batchsize for bulk uploads and also specifying option - useInternalTransaction, after that randomly I get Unexpected Existing Transaction. But if I try to bulkload the same xml from my machine, then it works fine with the same code + same DB

EDIT: 2013-12-06 One thing that I tried and that showed improvement is to pass an external transaction to SqlBulkCopy constructor. But I still do not understand why not having transaction would fail.

EDIT: 2013-12-24 The stack trace for ArgumentOutOfRange exception

 [ 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(BulkCopySimpleRes    ultSet internalResults)
  à System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
  à System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
  à System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)

Translates to

Index was out of range. It must not be negative and must be less than the size of the collection.

Also, having transaction does not change it. I thought it did but I do see errors when my aplication starts updating the files.

Jyotsna Sonawane
  • 303
  • 1
  • 3
  • 17
  • Where is the data coming from? i.e. what are you passing to `SqlBulkCopy`? is it a `DataTable`? or is it a custom `IDataReader`? or...? How many rows are we talking about? Have you defined explicit column mappings? If so, by name? Or by position? Do the columns (order, name, exact data type) match on the dev and prod servers? etc... – Marc Gravell Nov 27 '13 at 09:11
  • 1
    Also: no, that isn't SQL Server memory consumption - SQL Server won't throw OOMs. It sounds like your *application* is using a large amount of memory. That is possible during a bulk load, but can usually be avoided by appropriate design. – Marc Gravell Nov 27 '13 at 09:11
  • @MARC - It is a datatable. The input file is xml which is loaded into dataset and its DataTable is passed to sqlbulkcopy. Also I am saying that SQL starts throwing OutOfMemory because when I start getting the errors, if I execute even the simplest of the queries through management studio, I do see a message mentioning OutOfMemory exception has occurred. I have not seen this before this instance – Jyotsna Sonawane Nov 27 '13 at 10:04
  • and the number of rows is? – Marc Gravell Nov 27 '13 at 10:04
  • Also: is the `DataTable` schema (column order in particular) hard-coded? Depending on how you are reading the xml, if you are parsing it and adding columns dynamically, I can see lots of scenarios that could lead to the columns arriving in different orders – Marc Gravell Nov 27 '13 at 10:05
  • Column mappings have been provided for sqlbulkcopy. So I think order should not matter ! Number of rows vary depending on the size of XML. Sometimes in thousands. If I restart sql server service and take that request to execute separately on the test server, I do not see any problem. So I am guessing this is happening because SQL has already taken lot of memory. But I dont know how to be sure – Jyotsna Sonawane Nov 27 '13 at 10:08
  • SQL server usually blocks out memory in advance, and either way - it is very good at persisting to disk etc. I suspect the thing that is helping here is "take that request to execute separately on the test server", not the "restart sql server". Are the column mapping specified by name? or by position? – Marc Gravell Nov 27 '13 at 10:14
  • Column mappings are specified by name – Jyotsna Sonawane Nov 27 '13 at 10:24
  • *column mapping does not match in source or destination* Are you re-using the `SqlBulkCopy` object? *argument out of range exception* Normally an `ArgumentOutOfRangeException` will tell you which argument was out of range. Aside from that, do you handle dates as strings and the locale of your PC differs from the locale of the server? Getting an OOM exception suggests you're fragmenting memory and/or holding on to too many objects. I assume you have a 32-bit process and aren't trying to load > 2gb of data (although I've seen the GC start to struggle at 1.3gb). – ta.speot.is Dec 02 '13 at 04:13
  • @ta.speot.is Thanks for the response. Can you tell me how I can start towards getting more information ? ArgumentOutOfRange happens during SqlBulkCopy.WriteToServer - so I dont really know whats causing problem. Since I can successfully bulkcopy if I try later, I am sure the data is correct. for OOM - how can I get started to solve the problem? – Jyotsna Sonawane Dec 02 '13 at 05:38
  • @JyotsnaSonawane Get your hands on the `ArgumentOutOfRangeException` and look at [`ParamName`](http://msdn.microsoft.com/en-us/library/system.argumentexception.paramname(v=vs.110).aspx) and the stack trace. It should tell you where `SqlBulkCopy` is falling over. – ta.speot.is Dec 02 '13 at 05:49
  • I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders Dec 06 '13 at 08:01

0 Answers0