Could not allocate a new page for database 'TEMPDB' because the 'DEFAULT' filegroup is full due to lack of storage space or database files reaching the maximum allowed size.

Question

Hi,

Need help on this. When am running below query automatically tempdb is growing and local drive is getting full which is 70GB free. The actual size table is 5 Million.

with custmr_billed as (
    select
        cus_id,
        year([bill date]) as billed_yr
    from
        cu_base 
),
yearcohorts as (
    select
        cus_id,
        min(billed_yr) over (partition by cus_id) as cohortyear
    from
        custmr_billed
),
yearlyretention as (
    select
        cohortyear,
        billed_yr,
        count(distinct yc.cus_id) as retainedcustomers
    from
        yearcohorts yc
    join
        custmr_billed ub on yc.cus_id = ub.cus_id
    where
        ub.billed_yr >= yc.cohortyear
    group by
        cohortyear,
        billed_yr
),
yearlyretentionwithperiod as (
    select
        cohortyear,
        billed_yr,
        retainedcustomers,
        (billed_yr - cohortyear) + 1 as period
    from
        yearlyretention
),
cohortsizes as(
    select
        cohortyear,
        count(distinct cus_id) as cohortsize
    from yearcohorts
    group by cohortyear
)
select
    yrwp.cohortyear,
    yrwp.billed_yr,
    cs.cohortsize,
    yrwp.retainedcustomers,
    cast(yrwp.retainedcustomers as float) / cs.cohortsize  as retentionpercentage,
    (100.00 - cast(yrwp.retainedcustomers as float) / cs.cohortsize * 100)/100 as churnpercentage,
    cs.cohortsize - yrwp.retainedcustomers as churncount,
    yrwp.period
from
    yearlyretentionwithperiod yrwp
join cohortsizes cs on yrwp.cohortyear = cs.cohortyear
order by
    yrwp.cohortyear,
    yrwp.billed_yr;

ERROR

Could not allocate a new page for database 'TEMPDB' because the 'DEFAULT' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. 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.

Answer 1

Since I don't know your tables, it is difficult to suggest. But when things like this happens there is all reason to ask: is the query correct? Have you tested this with a smaller data set to verify the query?

As I said, I don't know your tables, but I get the feeling that cus_id is not a unique key in cu_base. And if so, this join:

from
        yearcohorts yc
    join
        custmr_billed ub on yc.cus_id = ub.cus_id

will cause a row explosion. Say that there are 100 rows for a customer. This join will then produce 10000 rows for that customer only. In the final result this may be masked by the GROUP BY, but before that happens you may have filled up tempdb.

I fail to see that there would be any reason to write the yearcohorts CTE this way:

select
        cus_id,
        min(billed_yr) over (partition by cus_id) as cohortyear
    from
        custmr_billed

If you write it as

SELECT cus_id, MIN(billed_yr) AS cohortyear
FROM  custmr_billed
GROUP BY cus_id

There will be no row explosion in the join in the next CTE.

Answer 2

Hi mrrobot telg

Try expanding tempdb files, optimizing query logic, and pre-allocating resources, the current space shortage issue can be resolved.

In the long term, it is recommended to optimize indexes and perform regular maintenance to prevent recurrence. If the issue persists, further analysis of Sort and Hash Match operations in the execution plan is needed for targeted optimization.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.