1

I am not very experienced so please bear with me.

I have been attempting to import a 2500 MB and 3800 MB CSV into SQL Server 2016. Unfortunately I keep getting the System.OutOfMemoryException error. My computer has 8.00 GB RAM so I figured I would just increase the max server memory from the default up to 4000 MB. For some reason though each time I try to change the max server memory to make it higher it changes back to default the moment I press OK. How do I fix this problem?

I am using BULK INSERT to import the data. The BATCHSIZE, KILOBYTES_PER_BATCH, and ROW_PER_BATCH arguments are not set.

UPDATE

I have decided to split up my CSV document so that I can import less of it at a time. Unfortunately by the time I try to import the last piece of my CSV the error shows up again.

UPDATE

I have managed to substantially increase the max server memory to 4 GB. However this has not fixed the problem.

Improve this question
0

1 Answer 1

Reset to default
4

The System.OutOfMemoryException error that you are seeing means there was a problem with SQL Server Management Studio, not with the amount of memory on the server itself. One easy way to trigger this error is to simply run a SELECT query on a large table. If the table is too large then the results grid will take up too much memory and you'll see the error.

It's not clear to me why you would experience this error when importing data, but raising max server memory is not the right fix. I recommend trying BULK INSERT after setting the BATCHSIZE, KILOBYTES_PER_BATCH, or ROW_PER_BATCH arguments. That will limit the amount of data sent in a single transaction. You could also try importing the data through bcp. You don't even need SSMS open for that.

Improve this answer
3
  • I have lessened the amount of rows I import at a time. Unfortunately by the time I import my last series of rows into the table I get the server memory issue all over again.
    – ana ng
    Commented Mar 7, 2017 at 22:14
  • @anang, I see that you marked my answer as accepted. Is your problem resolved? If not I think that we have a communication problem here. When I say "BULK INSERT" I mean using this command: msdn.microsoft.com/en-us/library/ms188365.aspx . Can you give us more details on how you are importing the data? Are you using SSMS? Which version? Are you generating scripts to insert the data?
    – Joe Obbish
    Commented Mar 8, 2017 at 0:27
  • I marked your answer as correct because I was able to find a way to increase the max server memory. I was also able to import the document in smaller batches. However when I attempted to use a UNION query to combine all the smaller tables (the results of my smaller batches) the error popped up again. So I guess technically the question was resolved as far as how I posed the question is concerned. Its just that the crux of the issue is still there. Sould I remove the checkmark for answer?
    – ana ng
    Commented Mar 8, 2017 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.