While pivoting lots of rows tempDB gets full in SQL server

Question

I have a query which retrieves around 184K rows and stores into Temporary table. Now, In second query I am first retrieving all the data from Temporary table and pivoting it. While pivoting I am getting below error.

Msg 1105, Level 17, State 2, Line 56 Could not allocate space for object 'dbo.WORKFILE GROUP large record overflow storage: 140761897762816' 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.

SQL Query as below:

        SELECT *
        FROM
        (
            SELECT Id,
                   Name,
                   ROW_NUMBER() OVER (PARTITION BY Id,
                                                   Name
                                      ORDER BY
                                          (
                                              SELECT NULL
                                          )
                                     ) AS [Row Number],
                   [Value]
            FROM #Data --184K Rows
        ) AS S
        PIVOT
        (
            MAX([Value])
            FOR Name IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L],[M],[N],[O],[P],[Q],[R]
                                   )
        ) AS PVT;

Can we resolve this issue without increasing the size of tempDB?

Answer 1

(Guessing since you didn't post sql or plan) If you are using PIVOT, it will generally end up with an aggregate and likely a streamagg. This can have a sort (which will go to tempdb). It is unlikely that this would run out of space on its own. However, the other pattern we see is to do a subselect per attribute SELECT (SELECT MAX(col1) FROM T WHERE col='value', …) FROM .... This could result in an aggregate that could be implemented by a sort per attribute. So, it is feasible that this could be the reason that you could run out of temp space on a query if you ended up trying to pivot a very large table. Please consider the pattern you are using to write the query. If your query plan has many spools/sorts in it, you might be able to move to PIVOT and reduce the number of sorts required.