Facing Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'

Question

Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Am facing this error when executing a stored procedure which has select statements which has nearly 250 million records.

Even though I have around 650gb space in tempdb am facing this error. Would like to know what I can do further on this

Query:

SELECT u.Id, u.place, u.name, u.lname, LOWER(ue.email) AS Email, MIN(dp.BirthTime) AS Time
FROM tableA u
    JOIN tableB ue ON ue.id = u.id AND ue.Did = 0
    JOIN tableC dp ON dp.Id = u.Id
        JOIN tableB dpe ON dpe.Id = dp.Id
            LEFT JOIN tableB idpe ON idpe.Email = dpe.Email
                LEFT JOIN tableE idp ON idp.Id = idpe.Id
                    LEFT JOIN tableD pidp ON pidp.Id = idp.Id 
        JOIN tableD cp ON dp.Id = cp.Id
where ISNULL(cp.FName,'') = '' AND ISNULL(cp.LName,'') = '' AND ISNULL(cp.IsActive,0) = 0 AND ISNULL(dp.Isinactive,0) = 0
    AND ISNULL(pidp.FName,'') = '' AND ISNULL(pidp.LName,'') = ''
    AND ISNULL(pidp.Isactive,0) = 0 AND ISNULL(idp.IsInactive,0)
    AND ISNULL(u.EHome, '') != ''
GROUP BY u.Id, u.Isactive, u.name, u.EServer, ue.Email

Paste here your execution plan. Something spills to tempdb, hash or sort. — sepupic, Mar 13 '19 at 14:27
How much space are these 250 million rows? That isn't all that large of a query but if this is pulling in varbinary(max) or other large rows that 650gb could get gobbled up easily. — Sean Lange, Mar 13 '19 at 14:42
The query has 4 joins and 3 left joins, so what I have to do ? — Navin, Mar 13 '19 at 15:51
Please provide query execution plan: https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan . — Alex, Mar 13 '19 at 21:05
@Alex Can you tell how can I allocate a new page for database 'TEMPDB' as am facing insufficient disk space in filegroup 'DEFAULT' — Navin, Mar 14 '19 at 07:17

Wes H · Accepted Answer · 2020-09-16T18:39:30.100

TempDB is not the problem.

The query has multiple filters with non-SARGable expressions. All of the joins must be processed before any of the where clause data can be filtered. If your end result (filtered and grouped) is 250 million rows, I expect the results after the joins would be in the high billions. That result must be materialized in tempdb before the filtering and grouping can occur.

Try changing this ISNULL(cp.FName,'') = '' to this (cp.FName = '' OR cp.FName IS NULL), and all similar expressions. This may at least allow some filtering to occur prior to the joins.

Second step is to remove the joins that are not used in the result set. You're only returning data from tables U, UE & DP. Move the other inner joins into exists clauses

EXISTS (SELECT 1 FROM tableB dpe WHERE dpe.Id = dp.Id)

This will reduce the initial join product to something more manageable.

Your left joins will be more complex to resolve, but should also not be a join if not part of the result set. I read your query to say you want to filter your results where data is missing. FName for example should only return if the FName is an empty string, a null value, or there is no matching record. Another way of saying that is you don't want to see records with FName, LName, IsActive populated with a nonzero/nonblank value.

AND NOT EXISTS ( SELECT 1 FROM tableD AS pidp where pidp.Id = idp.Id AND fname <> '' AND lname <> '' AND isactive = 0)

This statement will exclude them if all 3 are populated. If you only want to exclude them if any one of the fields has a value, change the AND to an OR inside the EXISTS clause.

Try these changes and I expect you'll avoid the bloated TempDB altogether.

Thanks a lot, it helped a lot and query took 15 mins to run after the changes. Thank you so much for quick help — Navin, Mar 22 '19 at 05:47
Glad it helped. If it has been resolved, can you select an answer so the question will be closed? — Wes H, Mar 22 '19 at 15:36

score 0 · Answer 2 · answered Mar 15 '19 at 12:54

I can see a few problems with the query that will make it quite inefficient:

LEFT JOIN tableD pidp - you left join this table and then apply filter conditions in WHERE clause:

ISNULL(pidp.FName,'') = ''AND ISNULL(pidp.LName,'') = '' AND ISNULL(pidp.Isactive,0) = 0

ISNULL([col name], '') != '' - very inefficient construct that ensures that any indexes (if present) on the column will never be used. If columns are not nullable than move filter conditions to the join and remove ISNULL:

ON pidp.Id = idp.Id AND pidp.FName = ''
    AND pidp.LName = '' AND pidp.Isactive = 0

This also applies to ISNULL(idp.IsInactive,0) parameter.

GROUP BY statement to fix problems caused by weak joins / bad query design - this is a sign of a really bad query design and most likely the sole reason for excessive temp table usage.

I would re-write query to something along these lines (I did not include recommendations in Step 1 as they depend on your specific table definition):

SELECT u.Id, u.place, u.name, u.lname, LOWER(ue.email) AS Email, MIN(dp.BirthTime) AS Time
FROM tableA u
    JOIN tableB ue ON ue.id = u.id AND ue.Did = 0
WHERE ISNULL(u.EHome, '') != ''
    AND u.Id IN(
        SELECT dp.id
        FROM tableC dp
            JOIN tableB dpe ON dpe.Id = dp.Id
                LEFT JOIN tableB idpe ON idpe.Email = dpe.Email
                    LEFT JOIN tableE idp ON idp.Id = idpe.Id
                        LEFT JOIN tableD pidp ON pidp.Id = idp.Id 
            JOIN tableD cp ON dp.Id = cp.Id
        where ISNULL(cp.FName,'') = '' AND ISNULL(cp.LName,'') = '' AND ISNULL(cp.IsActive,0) = 0 AND ISNULL(dp.Isinactive,0) = 0
            AND ISNULL(pidp.FName,'') = '' AND ISNULL(pidp.LName,'') = ''
            AND ISNULL(pidp.Isactive,0) = 0 AND ISNULL(idp.IsInactive,0)
         )

Facing Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'

2 Answers2