Thanks for reading, am a bit confused by this, think it is down to the size of TempDB, but unsure why it is happening in this case:
I have a MERGE statement:
DECLARE @LastUpdate DATETIME
SELECT @LastUpdate = ISNULL(MAX(LastUpdate),'1900-01-01') FROM dbo.StatusTable
DECLARE @CurTime DATETIME = GETDATE()
MERGE dbo.TableName AS targ
WHERE UpdateDateTime > @LastUpdate
) AS src
src.JoinFields = targ.JoinFields
WHEN MATCHED AND ISNULL(src.RemData,0) <> 1 THEN
fieldtoupdate = src.fieldtoupdate
WHEN NOT MATCHED BY TARGET THEN
WHEN MATCHED AND src.RemData = 1 THEN
OUTPUT $action INTO @MergeOutput;
Obviously the source is a subset of the main table (usually a smallish % of the table, lets say 80-100Gb [Table is TB sized]). TempDB is allocated 800Gb and is on it's own drive with no auto-growth. I am getting the following error:
Could not allocate space for object 'dbo.SORT temporary run storage: 140962158870528' 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.
Now, does this mean it tries to put the whole of the target table into TempDb? If so, I will obviously need to stop using merge and write separate Insert/update/delete statements. I have been looking for the answer to this in all the technicals for MERGE, but can't find anything about it, so any help is very much appreciated.
The answer to this was down to indexes, there wasn't a covering one on the target table due to the nature of the data. I am still trying to figure a way around this currently, but looks like surrogate keys and a proper indexing strategy.
Thanks for the comments.