Troubleshooting Guide: SQL Server 2022 'Could Not Allocate Space' (Msg 1105)
Quick Fix Summary
TL;DRCheck and expand the primary data file or tempdb filegroup, and verify available disk space.
SQL Server cannot allocate a new page or extent in a database file because the file is full or has reached its maximum size limit. This is a critical storage allocation failure that halts transactions.
Diagnosis & Causes
Recovery Steps
Step 1: Immediate Space Assessment
First, identify which database and file is out of space. This query shows file usage and growth settings.
SELECT DB_NAME(database_id) AS DatabaseName,
name AS LogicalName,
physical_name AS PhysicalFile,
(size*8)/1024 AS Size_MB,
FILEPROPERTY(name, 'SpaceUsed') * 8/1024 AS Used_MB,
((size - FILEPROPERTY(name, 'SpaceUsed')) * 8)/1024 AS Free_MB,
max_size,
growth,
is_percent_growth
FROM sys.master_files
WHERE DB_NAME(database_id) NOT IN ('master','model','msdb')
ORDER BY Free_MB ASC;Step 2: Check Disk Space
Verify the underlying storage volume has sufficient free space for file growth. Use xp_fixeddrives.
EXEC xp_fixeddrives;Step 3: Expand the Critical File
If disk space is available, expand the identified full file. Replace 'YourDB' and 'YourDB_Data' with actual names.
USE master;
GO
ALTER DATABASE [YourDB] MODIFY FILE ( NAME = N'YourDB_Data', SIZE = 10240MB ); -- Increase to 10GB
GOStep 4: Clear Tempdb Space (If Culprit)
If tempdb is full, restarting the SQL Server service is the fastest fix. For non-disruptive checks, identify and kill long-running queries using tempdb.
-- Identify sessions using significant tempdb space
SELECT t1.session_id,
t1.internal_objects_alloc_page_count,
t1.user_objects_alloc_page_count,
t.text AS QueryText
FROM sys.dm_db_session_space_usage t1
INNER JOIN sys.dm_exec_sessions AS t2 ON t1.session_id = t2.session_id
INNER JOIN sys.dm_exec_requests AS t3 ON t2.session_id = t3.session_id
CROSS APPLY sys.dm_exec_sql_text(t3.sql_handle) t
ORDER BY (t1.internal_objects_alloc_page_count + t1.user_objects_alloc_page_count) DESC;Step 5: Enable Autogrowth with Safe Settings
Prevent recurrence by configuring sensible autogrowth. Avoid small percent-based growth on large files.
ALTER DATABASE [YourDB] MODIFY FILE ( NAME = N'YourDB_Data', FILEGROWTH = 512MB, MAXSIZE = UNLIMITED );Step 6: Proactive Monitoring Setup
Create an alert to monitor file free space. This example checks for files with less than 10% free space.
-- Run in a scheduled job or agent job
DECLARE @AlertMessage NVARCHAR(MAX);
SET @AlertMessage = '';
SELECT @AlertMessage = @AlertMessage +
'Database: ' + DB_NAME(database_id) + ', File: ' + name + ' is ' + CAST(CAST((((size - FILEPROPERTY(name, 'SpaceUsed')) * 100.0) / size) AS DECIMAL(5,2)) AS VARCHAR) + '% free.' + CHAR(10)
FROM sys.master_files
WHERE CAST((((size - FILEPROPERTY(name, 'SpaceUsed')) * 100.0) / size) AS DECIMAL(5,2)) < 10.0
AND size > 0;
IF @AlertMessage <> ''
BEGIN
-- Logic to send email alert (e.g., via Database Mail)
PRINT @AlertMessage;
ENDArchitect's Pro Tip
"For VLDBs, pre-allocate files during maintenance windows. Autogrowth events are synchronous and can cause transaction blocking, making proactive sizing critical for performance."
Frequently Asked Questions
Can I just shrink the database file to fix error 1105?
No. Shrinking is a destructive, last-resort operation that causes massive fragmentation and is often temporary. The correct fix is to add space or expand the file.
Why does the error point to a specific object ID?
The object ID in the error message is the internal ID of the system table (often in tempdb) or user object that SQL Server was trying to allocate space for when it hit the limit.
My disk has plenty of free space. Why am I still getting this error?
Check the MAXSIZE property of the database file. The file may have hit its configured maximum size limit, even though the disk has space. Use ALTER DATABASE MODIFY FILE to increase MAXSIZE.