Could Not Allocate Space Because the Filegroup Is Full (Error 1105)

This is the data file, not the log. If you have arrived here after reading about transaction log full, that is a different error (9002) with a completely different fix, and taking a log backup will do nothing for this one.

The message lists 3 fixes without telling you which one you need, and older builds listed a 4th, deleting unneeded files from the disk. The difference matters, because adding a file and switching autogrowth on are fine, and dropping objects in a hurry is how people make things worse.

Find Out Which Kind of Full It Is

There are 3, and they look identical from the error:

SELECT  f.name              AS logical_name,
        f.physical_name,
        fg.name             AS filegroup_name,
        f.size / 128.0                                   AS size_mb,
        CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS INT) / 128.0 AS used_mb,
        (f.size - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS INT)) / 128.0 AS free_mb,
        f.growth,
        f.is_percent_growth,
        CASE f.max_size
             WHEN -1 THEN 'unlimited'
             WHEN  0 THEN 'no growth'
             ELSE CAST(f.max_size / 128 AS VARCHAR(20)) + ' MB cap'
        END                 AS max_size
FROM    sys.database_files f
JOIN    sys.filegroups fg ON fg.data_space_id = f.data_space_id
WHERE   f.type_desc = 'ROWS';

Read it in this order:

• growth is 0. Autogrowth is switched off. The file will never grow, however much disk you have. This is the most common cause and the least obvious, because the disk looks fine.
• max_size is a cap and used has reached it. The file hit a ceiling somebody set deliberately, or a ceiling nobody knew about.
• growth is set and max_size is unlimited. Then it tried to grow and could not, which means the disk is out of space.

Check the Disk Before Changing Anything

SELECT DISTINCT
       vs.volume_mount_point,
       vs.total_bytes  / 1073741824.0 AS total_gb,
       vs.available_bytes / 1073741824.0 AS free_gb,
       CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5,1)) AS pct_free
FROM   sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs;

If the volume has room and the file still will not grow, it is autogrowth or a cap. If the volume is full, no setting change will help and you need space first.

Microsoft’s reference covers error 1105 and ALTER DATABASE file and filegroup options in full.

The Fixes, and Which To Reach For

Autogrowth Is Off, or the Cap Is Reached

ALTER DATABASE [YourDb]
MODIFY FILE (NAME = N'YourDb', FILEGROWTH = 512MB, MAXSIZE = UNLIMITED);

Set growth in megabytes, not percent. Percentage growth on a large file means each growth is bigger than the last, and every one of them is a pause while the file extends.

The Disk Is Genuinely Full

Add a file on a different volume rather than fighting for space on a full one:

ALTER DATABASE [YourDb]
ADD FILE (NAME = N'YourDb_2',
          FILENAME = N'E:\Data\YourDb_2.ndf',
          SIZE = 8GB, FILEGROWTH = 512MB)
TO FILEGROUP [PRIMARY];

SQL Server spreads new allocations across files in the filegroup, so this relieves the pressure immediately.

A second file is a real decision, not a quick fix. It stays part of the database forever and it has to be backed up, restored and managed with it. If it is genuinely temporary, plan how it comes back out.

What Not To Do

Do not shrink another database to make room. It causes index fragmentation, the space comes back almost immediately, and you now have two problems.

Do not delete data to free space in a hurry. A delete inside a transaction needs log space and frees no data-file space until the pages are deallocated, so it can make the outage worse at the exact moment you need it not to.

When It Is Not Really About Space

A file with plenty of free space that still throws 1105 usually means the free space is in the wrong place:

• TempDB Is Full or Still Growing: Find What Is Using It, Right Now, the post that names the conversation and hands it over
• A different filegroup. The object lives in a filegroup that is full while PRIMARY has room, or the reverse. The error names the filegroup, so read it rather than assuming PRIMARY.
• TempDB. The same error against tempdb is a different conversation, usually a query spilling far more to disk than anyone expected.
• Uneven files. Several files in the filegroup, one full and the others not, because they were added at different sizes. SQL Server fills proportionally to free space, so a small new file gets hammered.

Stopping It Recurring

• Alert on free space inside the file, not just on the volume. A file with autogrowth off can be full on a half-empty disk, and volume monitoring will never see it.
• Size files for the year ahead and leave them there. Autogrowth is a safety net, not a capacity plan, and every growth event is a pause.
• Turn on Instant File Initialization so data file growth is near-instant rather than a stall while the file is zeroed.
• Check growth = 0 across the estate. It is usually a leftover from a restore or a template database, and nobody finds it until the file fills.

Common Questions

Related Scripts

• Disk Is Full or the Log Will Not Truncate, the drive-level triage that comes before this entry
• Get Filegroup Space, which filegroup is full and how much room the others have
• Get SQL Server Database File Details, growth settings and size caps for every file, the columns that decide which kind of full this is
• The Filegroup Has No Files Assigned to It (Error 622), the other filegroup error, where the problem is that there is no file at all
• Get Database Sizes and Free Space, where every file stands right now
• Get Disk Space on SQL Server, the volume-level view
• Get Autogrowth History, how often files have grown and by how much
• Transaction Log Full (9002), the log-side error this gets confused with