Error 1105: Could Not Allocate Space for Object — Expand Filegroup or Disk

 

Introduction

SQL Server Error 1105: Could not allocate space for object '<object_name>' in database '<database_name>' because the '<filegroup_name>' filegroup is full. Disk space or maximum size may need to be increased. occurs when SQL Server cannot allocate space for a new object or data in a filegroup due to insufficient disk space or filegroup size limits. This guide provides code-oriented solutions to diagnose and resolve Error 1105 by expanding filegroups, increasing disk space, or optimizing storage.

---

1. Check Filegroup and File Space Usage

Identify the filegroup and database files causing the error.

Code-Oriented Solution

Check filegroup and file space usage:

-- View filegroup space usage
SELECT 
    fg.name AS FilegroupName,
    f.name AS FileName,
    f.physical_name AS FilePath,
    (f.size * 8.0 / 1024) AS SizeMB,
    (f.max_size * 8.0 / 1024) AS MaxSizeMB,
    (f.size * 8.0 / 1024) - (f.space_used * 8.0 / 1024) AS FreeSpaceMB
FROM sys.filegroups fg
JOIN sys.database_files f ON fg.data_space_id = f.data_space_id
WHERE fg.name = 'YourFilegroupName'; -- Replace with filegroup from error

Check database file sizes and growth settings:

-- View database file details
SELECT 
    name AS FileName,
    physical_name AS FilePath,
    type_desc AS FileType,
    (size * 8.0 / 1024) AS SizeMB,
    (max_size * 8.0 / 1024) AS MaxSizeMB,
    growth,
    is_percent_growth
FROM sys.database_files;

Action:

• Note if the filegroup is full (FreeSpaceMB near zero) or if max_size is reached.

• Check the disk drive for available space using PowerShell:

# Check disk space
Get-Disk | Get-Partition | Get-Volume | Select-Object DriveLetter, SizeRemaining, Size

Why? Error 1105 occurs when the filegroup or disk is full, or the file’s maximum size limit is reached.

---

2. Expand Database File Size

Increase the size of the database file in the affected filegroup.

Code-Oriented Solution

Increase the file size:

-- Expand file size
ALTER DATABASE YourDatabaseName
MODIFY FILE (
    NAME = 'YourFileName', -- Logical file name from sys.database_files
    SIZE = 1024MB, -- New size (e.g., 1GB)
    FILEGROWTH = 100MB -- Optional: Set autogrowth increment
);

Enable unrestricted growth if limited by max_size:

-- Set unlimited growth
ALTER DATABASE YourDatabaseName
MODIFY FILE (
    NAME = 'YourFileName',
    MAXSIZE = UNLIMITED
);

Action:

• Ensure the disk has enough free space before expanding.

• Verify the change:

SELECT 
    name AS FileName,
    (size * 8.0 / 1024) AS SizeMB,
    (max_size * 8.0 / 1024) AS MaxSizeMB
FROM sys.database_files
WHERE name = 'YourFileName';

Why? Increasing the file size or allowing unlimited growth resolves space allocation issues if disk space is available.

---

3. Add a New File to the Filegroup

If the disk is full or the file cannot grow, add a new file to the filegroup on a different disk.

Code-Oriented Solution

Add a new file to the filegroup:

-- Add a new file to the filegroup
ALTER DATABASE YourDatabaseName
ADD FILE (
    NAME = 'YourFileName_New',
    FILENAME = 'D:\SQLData\YourFileName_New.ndf', -- Path to new disk with free space
    SIZE = 1024MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 100MB
) TO FILEGROUP YourFilegroupName;

Action:

• Verify the new file:

SELECT 
    name AS FileName,
    physical_name AS FilePath,
    (size * 8.0 / 1024) AS SizeMB
FROM sys.database_files
WHERE name = 'YourFileName_New';

• Ensure the new file’s disk has sufficient free space.

Why? Adding a file on a disk with available space allows the filegroup to accommodate more data.

---

4. Free Up Disk Space

If the disk is full, free up space or move files to a larger disk.

Code-Oriented Solution

Check disk usage with PowerShell:

# List large files on the disk
Get-ChildItem -Path "C:\SQLData" -Recurse | 
Sort-Object Length -Descending | 
Select-Object Name, @{Name="SizeMB";Expression={$_.Length / 1MB}} -First 10

Shrink non-critical database files to free space (use cautiously):

-- Shrink a log file
USE YourDatabaseName;
GO
DBCC SHRINKFILE ('YourLogFileName', 100); -- Shrink to 100MB

Action:

• Delete unnecessary files or move database files to a larger disk:

-- Detach database
EXEC sp_detach_db 'YourDatabaseName';

-- Move file to new disk (manually via file system)

-- Re-attach database
EXEC sp_attach_db 
    @dbname = 'YourDatabaseName',
    @filename1 = 'D:\NewSQLData\YourFileName.mdf',
    @filename2 = 'D:\NewSQLData\YourFileName_New.ndf',
    @filename3 = 'D:\NewSQLData\YourLogFileName.ldf';

• Add more disk space via server or storage management tools.

Why? A full disk prevents file growth, triggering Error 1105.

---

5. Optimize Database Storage

Reduce space usage by removing unnecessary data or optimizing objects.

Code-Oriented Solution

Identify large objects consuming space:

-- Find largest tables/indexes
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    SUM(a.total_pages * 8.0 / 1024) AS SizeMB
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY i.object_id, i.name
ORDER BY SizeMB DESC;

Shrink the database (use sparingly):

-- Shrink database
DBCC SHRINKDATABASE (YourDatabaseName, 10); -- 10% free space target

Rebuild indexes to reduce fragmentation:

-- Rebuild large index
ALTER INDEX ALL ON YourTableName REBUILD;

Action:

• Delete unneeded data:

-- Delete old records (example)
DELETE FROM YourTableName
WHERE CreatedDate < '2023-01-01';

• Archive old data to another database or table to free space.

Why? Optimizing storage reduces filegroup usage, preventing Error 1105.

---

6. Adjust Autogrowth Settings

Ensure appropriate autogrowth settings to prevent frequent allocation failures.

Code-Oriented Solution

Set reasonable autogrowth increments:

-- Modify file autogrowth
ALTER DATABASE YourDatabaseName
MODIFY FILE (
    NAME = 'YourFileName',
    FILEGROWTH = 100MB -- Set to fixed size (e.g., 100MB) instead of percentage
);

Action:

• Avoid percentage-based growth for large files to prevent excessive allocation delays.

• Verify settings:

SELECT 
    name AS FileName,
    (growth * 8.0 / 1024) AS GrowthMB,
    is_percent_growth
FROM sys.database_files;

Why? Inappropriate autogrowth settings can cause allocation delays or failures.

---

7. Monitor and Prevent Future Issues

Set up monitoring to detect low space conditions proactively.

Code-Oriented Solution

Create an alert for low disk space:

-- Create SQL Server Agent alert for Error 1105
EXEC msdb.dbo.sp_add_alert 
    @name = N'Low Disk Space - Error 1105',
    @message_id = 1105,
    @severity = 0,
    @enabled = 1,
    @notification_message = N'Error 1105: Filegroup full. Check disk space and filegroup settings.',
    @include_event_description_in = 1;

-- Add notification (e.g., email)
EXEC msdb.dbo.sp_add_notification 
    @alert_name = N'Low Disk Space - Error 1105',
    @operator_name = N'YourOperatorName',
    @notification_method = 1; -- Email

Monitor filegroup space regularly:

-- Monitor filegroup space usage
SELECT 
    fg.name AS FilegroupName,
    SUM(f.size * 8.0 / 1024) AS TotalSizeMB,
    SUM((f.size - f.space_used) * 8.0 / 1024) AS FreeSpaceMB
FROM sys.filegroups fg
JOIN sys.database_files f ON fg.data_space_id = f.data_space_id
GROUP BY fg.name;

Action:

• Set up SQL Server Agent jobs to run the monitoring query periodically.

• Plan disk and filegroup expansions based on growth trends.

Why? Proactive monitoring prevents recurring Error 1105 issues.

---

8. Test with SQLCMD or SSMS

Test database operations to confirm resolution.

Code-Oriented Solution

Test with sqlcmd:

# Test INSERT into affected table
sqlcmd -S sqlserverhostname -U YourLoginName -P YourPassword -d YourDatabaseName -Q "INSERT INTO YourTableName (Column1) VALUES ('TestValue')"

In SSMS, run:

-- Test INSERT
INSERT INTO YourTableName (Column1)
VALUES ('TestValue');

Action:

• If the error persists, verify disk space and filegroup settings.

• Update application code to handle space issues gracefully:

using System.Data.SqlClient;

string connString = "Server=sqlserverhostname;Database=YourDatabaseName;Integrated Security=True;";
try {
    using (SqlConnection conn = new SqlConnection(connString)) {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("INSERT INTO YourTableName (Column1) VALUES (@Value)", conn)) {
            cmd.Parameters.AddWithValue("@Value", "TestValue");
            cmd.ExecuteNonQuery();
            Console.WriteLine("Insert successful!");
        }
    }
} catch (SqlException ex) when (ex.Number == 1105) {
    Console.WriteLine("Error 1105: Filegroup full. Check disk space or expand filegroup.");
    // Notify admin or trigger filegroup expansion
} catch (Exception ex) {
    Console.WriteLine($"Error: {ex.Message}");
}

Why? Testing confirms the resolution and helps identify application-specific issues.

---

Conclusion

SQL Server Error 1105 (Could not allocate space) can be resolved by expanding filegroup files, adding new files, freeing disk space, or optimizing storage. Use the provided T-SQL and code examples to diagnose and fix the issue systematically. 

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone