[SOLVED] Fixed Size TempDB (800Gb), MERGE into very large table failing due to space, is this a limitation of merge?

Fixed Size TempDB (800Gb), MERGE into very large table failing due to space, is this a limitation of merge?

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()

BEGIN TRANSACTION

    MERGE dbo.TableName AS targ
    USING
        (
            SELECT
                <fieldlist>
            FROM 
                JoinTablesEtc
            WHERE UpdateDateTime > @LastUpdate
        ) AS src
    ON
        src.JoinFields = targ.JoinFields
    WHEN MATCHED AND ISNULL(src.RemData,0) <> 1 THEN
        UPDATE
        SET
            fieldtoupdate = src.fieldtoupdate
    WHEN NOT MATCHED BY TARGET THEN
        INSERT
            (
                FieldList
            )
        VALUES
            (
                src.FieldList
            )
    WHEN MATCHED AND src.RemData = 1 THEN
        DELETE
    OUTPUT $action INTO @MergeOutput;
COMMIT

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.

Thanks

Solution

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.