Tempdb is configured with too small a maximum size and the disk is full. Large sorts, hash joins, or spills fill tempdb during an ETL-intensive operation. Too many temp tables or table variables created simultaneously

Step 1: Check tempdb space: SELECT volume_mount_point, available_bytes/1024/1024 as free_mb FROM sys.dm_os_volume_stats(2, 1);. Step 2: Expand tempdb: ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 10GB, MAXSIZE = UNLIMITED);. Step 3: Identify which sessions are consuming tempdb: SELECT session_id, SUM(user_object_reserved_page_count)*8/1024 as user_mb FROM sys.dm_db_task_space_usage GROUP BY session_id ORDER BY user_mb DESC;

High severityresource

Power BI Refresh Error:
1101

What does this error mean?

SQL Server cannot allocate a new page in tempdb — the tempdb database is full.

Common causes

1Tempdb is configured with too small a maximum size and the disk is full
2Large sorts, hash joins, or spills fill tempdb during an ETL-intensive operation
3Too many temp tables or table variables created simultaneously

How to fix it

1Step 1: Check tempdb space: SELECT volume_mount_point, available_bytes/1024/1024 as free_mb FROM sys.dm_os_volume_stats(2, 1);
2Step 2: Expand tempdb: ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 10GB, MAXSIZE = UNLIMITED);
3Step 3: Identify which sessions are consuming tempdb: SELECT session_id, SUM(user_object_reserved_page_count)*8/1024 as user_mb FROM sys.dm_db_task_space_usage GROUP BY session_id ORDER BY user_mb DESC;

Frequently asked questions

How many tempdb files should I have?

Best practice: one tempdb data file per logical CPU core, up to 8 files. This reduces latch contention on the PFS, GAM, and SGAM pages.

Why does tempdb fill up during an ADF COPY activity?

ADF may cause SQL Server to spill sort and hash operations to tempdb if there is insufficient memory for in-memory operations. Add indexes on join and sort columns to reduce spills.

Does Azure SQL Database use tempdb?

Yes, but Azure SQL manages tempdb automatically per service tier. If tempdb fills on Azure SQL, you need to scale up the service tier or optimize queries.

Official documentation: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-1101-database-engine-error