0

I am using a MERGE Query that is INSERTING over 800 Million records into a table from another table in the same database (conversion project). We run into this error below when it get's to this particular table it has to write to for the SQL Merge.

2019-02-05 16:35:03.002 Error Could not allocate space for object 'dbo.SORT temporary run storage: 140820412694528' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. 

  MERGE dbo.' +  @p_TargetDLTable + ' as TARGET
  USING dbo.' +  @p_SourceDLSDTable + ' as SOURCE
  ON (TARGET.docid = source.docid AND TARGET.objectid = source.objectid AND 
  target.pagenum = source.pagenum
  and target.subpagenum = source.subpagenum and target.pagever = 
  source.pagever and target.pathid = source.pathid
  and target.annote = source.annote)

 WHEN NOT MATCHED BY TARGET AND source.clipid != ''X''
 THEN INSERT (docid, pagenum, subpagenum, pagever, objectid, pathid, annote, 
 formatid, ftoffset, ftcount) VALUES (
 source.docid, source.pagenum, source.subpagenum, source.pagever, 
 source.objectid, source.pathid,source.annote ,source.formatid 
,source.ftoffset, source.ftcount); '

The reason I decided to use a MERGE query over INSERT INTO was because all the research was pointing to for the type of join that had to be done, it would result in faster performance.

Is there a way to increase the TempDB, or is there a way for the Merge to not have to use the TempDB? Does the INSERT INTO query also use the TempDB?

MikeGen18
  • 49
  • 7
  • Sure, you can add disk space and then increase the file size... but a batch UPSERT would server you better. 800 million is a lot at one time. TEMPDB is a dumpster that's used for all sorts of things (variables, temp tables, query spills, sorting, etc). Check this too for merge: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – S3S Feb 06 '19 at 22:28
  • I've heard of a lot of reasons _not_ to use `MERGE` (i.e. the link in the prior comment). I don't see anything special in your join condition. There's a lot of advice about tuning large data inserts but I've never heard `MERGE` mentioned. In the first instance I suggest you do use `INSERT` instead of `MERGE` and implement batching as already indicated. You'll probably still require TEMPDB regardless. – Nick.Mc Feb 06 '19 at 23:06
  • What is your current tempdb size? – sepupic Feb 07 '19 at 08:08

0 Answers0