SQL Server Error:
1101

What does this error mean?

SQL Server 1101 is thrown when the engine tries to allocate a new page in tempdb but cannot — either the tempdb data files have hit their configured MAXSIZE, the underlying disk volume has no free space, or autogrowth is disabled. In a data-pipeline context this typically surfaces mid-execution: an ADF Copy Activity or Mapping Data Flow triggers a large sort or hash join that spills to disk, tempdb fills up, and the entire pipeline run is killed with a resource-allocation error. The engineer sees a failed pipeline run with no partial output — SQL Server rolls back the incomplete transaction automatically.

Common causes

• 1Tempdb data files are configured with a hard MAXSIZE cap (e.g. 10 GB) and the disk volume has no room to grow — autogrowth events are silently blocked once the ceiling is hit.
• 2ADF Mapping Data Flow or SSIS packages perform large multi-way joins or ORDER BY operations that exceed the SQL Server memory grant and spill intermediate results to tempdb; on large datasets these spills can consume tens of gigabytes in minutes.
• 3Multiple ADF pipelines run concurrently against the same SQL Server instance — each pipeline opens its own tempdb session objects, and the combined allocation exceeds available space with no single pipeline being the obvious culprit.
• 4Poorly written ETL stored procedures create and drop #temp tables in a loop, or leave temp tables open across batch boundaries; leaked temp tables accumulate over the course of a long pipeline run and are only cleaned up when the session closes.
• 5Row-version store in tempdb grows unexpectedly when READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION is enabled and a long-running transaction holds open row versions — common when ADF pipelines run alongside interactive SSMS sessions with open transactions.
• 6Tempdb is configured with too few data files (often just one), causing severe allocation-page latch contention under concurrent load; SQL Server throttles sessions waiting for SGAM/GAM/PFS pages, and pending allocations pile up until the disk is exhausted.
• 7Autogrowth increment is set to a small fixed value (e.g. 1 MB default from legacy installs) — each growth event locks tempdb briefly, and under heavy load dozens of micro-growths occur in sequence until disk space runs out entirely.

How to fix it

1. 1Step 1 — Check current tempdb space and disk availability: SELECT f.name, f.physical_name, f.size*8/1024 AS size_mb, f.max_size, v.available_bytes/1048576 AS disk_free_mb FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) v WHERE f.database_id = DB_ID('tempdb');
2. 2Step 2 — Identify which sessions are consuming tempdb right now: SELECT s.session_id, s.login_name, s.host_name, s.program_name, (t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count) * 8 / 1024 AS tempdb_mb FROM sys.dm_db_session_space_usage t JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id WHERE (t.user_objects_alloc_page_count + t.internal_objects_alloc_page_count) > 0 ORDER BY tempdb_mb DESC; — kill runaway sessions with KILL <session_id> after confirming with the pipeline owner.
3. 3Step 3 — Expand existing tempdb data files immediately (no restart required): ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 20480MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB); — set MAXSIZE = UNLIMITED only if the disk can support it; otherwise set an explicit ceiling with monitoring.
4. 4Step 4 — Add additional tempdb data files up to the number of logical CPU cores (max 8) to reduce allocation-page contention: ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\tempdb\tempdev2.ndf', SIZE = 20480MB, FILEGROWTH = 1024MB); — repeat for tempdev3 … tempdev8 as needed, keeping all files the same initial size.
5. 5Step 5 — Check for row-version store bloat if snapshot isolation is in use: SELECT SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb FROM sys.dm_db_file_space_usage WHERE database_id = 2; — if this is large, find the blocking transaction: SELECT transaction_id, elapsed_time_seconds FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;
6. 6Step 6 — Reduce query spills by adding missing indexes on join and sort columns used by the failing pipeline: run the query from the ADF activity against SSMS with SET STATISTICS IO ON and look for 'Workfile' or 'Worktable' in the execution plan; add a covering index on the high-cardinality sort column. In ADF Mapping Data Flow, enable 'Optimize' → 'Single partition' on transformations that don't need parallelism to reduce simultaneous spill threads.
7. 7Step 7 — Schedule a tempdb health check query as a SQL Agent job or MetricSign alert to fire when free_mb drops below a threshold: SELECT volume_mount_point, available_bytes/1048576 AS free_mb FROM sys.dm_os_volume_stats(2, 1) WHERE available_bytes/1048576 < 5120; — 5 GB headroom is a reasonable minimum for ETL-heavy servers; tune to your pipeline's peak spill size.

Example log output

Msg 1101, Level 17, State 12, Line 1Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'PRIMARY'. 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.Error in ADF activity 'CopyToStagingTable': ErrorCode=SqlOperationFailed, Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=A database operation failed with the following error: 'Could not allocate space for object '#sort_worktable' in database 'tempdb'...'

Frequently asked questions

Other resource errors