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

Answer 1

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.