1

I need to execute a large Insert script of size 75MB in my database. I am using the built in SQL command tool to run this script, but it still throws the same error - "There is insufficient system memory in resource pool 'internal' to run this query."

sqlcmd -S .\SQLEXPRESS -d TestDB -i C:\TestData.sql

How to resolve this memory issue, when the last resort of running the script through SQLCMD does not work?

Note - Increasing the Maximum Server Memory(in the Server Properties) did not resolve this problem.

Sammy
  • 798
  • 2
  • 8
  • 23
  • 3
    break it into pieces........ – Mitch Wheat Dec 21 '16 at 04:18
  • Is there any other way to make this script work, other than batching the entries? I don't want to manually batch this enormous query every time. I intend to run this Insert script very frequently. – Sammy Dec 21 '16 at 04:22
  • what version of sql server are you on? – ydoow Dec 21 '16 at 04:32
  • Possible duplicate of [There is insufficient system memory in resource pool 'default' to run this query. on sql](http://stackoverflow.com/questions/25266663/there-is-insufficient-system-memory-in-resource-pool-default-to-run-this-query) – ydoow Dec 21 '16 at 04:33
  • @ydoow -- I am on SQL Server 2008 R2 – Sammy Dec 21 '16 at 04:36
  • @ydoow - It is not a duplicate. Increasing the Maximum Server Memory(in the Server Properties) did not resolve this problem. – Sammy Dec 21 '16 at 04:41
  • Have you considered using bcp instead of insert clauses? – James Z Dec 21 '16 at 16:51

1 Answers1

4

I face the same issue recently. What I have done is added Go statements for every 1000 inserts. This worked perfectly for me.

Go statement will divide the statements into separate batches. So every batch treated as separate Insertion. Hope this will help you in some way.

Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
  • Earlier I tried with the GO statements and it did work for me. Is there any other way to make this script work, other than batching the entries? I don't want to manually batch this enormous query every time. I intend to run this Insert script very frequently. Also were you able to execute the modified query(With GO statements) through SSMS? – Sammy Dec 21 '16 at 04:35
  • So did you manually insert all the GO statements for every 1000 statements? I have more than million insert statements, I dont want to do this manually. Also I intend to run this script very frequently. – Sammy Dec 21 '16 at 05:21
  • No, I have not done insertion manually. SSMS itself executed it successfully. However putting `Go ` statement for a million of records is another pain. – Shakeer Mirza Dec 21 '16 at 05:31