Error 701 There is insufficient system memory ...

• Rich96

  SSC Eights!

  Points: 947

  More actions

  April 30, 2009 at 4:02 pm

  #131854

  I am curious to know how query analyzer treats a script with regards to Procedure Cache/memory usage/etc... The issue I was running into was the error 701 'There is insufficient system memory'... when executing a sql script that had over 47,000 lines of

  Delete From OBJECTS where ID = '38dkd8dkd8dkf8aks'

  Delete From OBJECTS where ID = '38dkd8drrrrrsdf8aks'

  Delete From OBJECTS where ID = '381234wertdkf8aks'

  Delete From OBJECTS where ID = 'hjuikli8889sdkf8aks'

  Delete From OBJECTS where ID = 'qweasodfijnno8890'

  Delete From OBJECTS where ID = '9073jd7xxklsa7sls7'

  Delete From OBJECTS where ID = 'jxnjos09sksdf9898n'

  ......

  (wasn't my choice, vendor provided).

  I changed the script to use a BULK insert into a temp table and then did my delete. It worked great.

  if OBJECT_ID('tempdb..#BulkImport') is not null DROP TABLE #BulkImport

  CREATE TABLE #BulkImport(

  LineText varchar(100)

  )

  BULK INSERT #BulkImport FROM 'C:\Temp\LIST_FOR_DELETE.txt'

  DELETE FROM OBJECTS WHERE oid in (SELECT LineText FROM #BulkImport)

  DROP TABLE #BulkImport

  How do each of these queries affect memory/performance and why would my method work and the other method errors?

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  April 30, 2009 at 10:07 pm

  #987140

  See BOL's topic "SQL Server Batch or Task Scheduling".

  The vendor's way made 47,000+ batches in one execution plan. That may have exhausted all of the available worker threads.

  Your method used only one batch.

  If the vendor put GO after every few hundred statements you probably would have been ok.

• Rich96

  SSC Eights!

  Points: 947

  More actions

  May 1, 2009 at 7:12 am

  #987268

  Thanks ksullivan!!! I'll check out BOL's topic "SQL Server Batch or Task Scheduling".