Data File Full and Disk Space (Error 1105)

October 9, 2026
| No Comments

Error 1105 means SQL Server could not allocate space because a filegroup is full. Error 9002 is the transaction log equivalent (see Transaction Log Full). Either the file has hit its maximum size or growth setting, or the volume itself is full.

Step 1: File sizes, space used and growth settings

USE [YourDB];
SELECT f.name, f.type_desc, fg.name AS filegroup_name, f.physical_name,
       CAST(f.size AS bigint) * 8 / 1024 AS size_mb,
       CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS bigint) * 8 / 1024 AS used_mb,
       CASE WHEN f.max_size = -1 THEN 'Unlimited'
            ELSE CAST(CAST(f.max_size AS bigint) * 8 / 1024 AS varchar(20)) END AS max_size_mb,
       CASE WHEN f.is_percent_growth = 1 THEN CAST(f.growth AS varchar(10)) + '%'
            ELSE CAST(CAST(f.growth AS bigint) * 8 / 1024 AS varchar(20)) + ' MB' END AS growth
FROM sys.database_files f
LEFT JOIN sys.filegroups fg ON f.data_space_id = fg.data_space_id;

Step 2: Free space on the volumes

SELECT DISTINCT vs.volume_mount_point, vs.logical_volume_name,
       vs.total_bytes / 1073741824 AS total_gb,
       vs.available_bytes / 1073741824 AS free_gb
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY vs.volume_mount_point;

Step 3: What is taking the space

USE [YourDB];
SELECT TOP (20) s.name AS schema_name, t.name AS table_name,
       SUM(ps.reserved_page_count) * 8 / 1024 AS reserved_mb,
       SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS row_count
FROM sys.dm_db_partition_stats ps
JOIN sys.tables t ON ps.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY s.name, t.name
ORDER BY reserved_mb DESC;

Step 4: Fix (NEEDS APPROVAL)

  • File at its maximum size, or growth set to 0: raise the limit. Rollback: set MAXSIZE back to its previous value (only possible while the file is still below it).
ALTER DATABASE [YourDB]
MODIFY FILE (NAME = N'YourDataFile', MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB);
  • Volume full: free space on the volume (old backups, dumps, other files) or ask for the disk to be extended. As a short-term measure, add a file on another volume:
ALTER DATABASE [YourDB]
ADD FILE (NAME = N'YourDB_Data2', FILENAME = N'E:\SQLData\YourDB_Data2.ndf',
          SIZE = 10240MB, FILEGROWTH = 1024MB)
TO FILEGROUP [PRIMARY];

Rollback for the extra file, once the original volume has space again:

USE [YourDB];
DBCC SHRINKFILE (N'YourDB_Data2', EMPTYFILE);
ALTER DATABASE [YourDB] REMOVE FILE [YourDB_Data2];

In an availability group the new file’s path must exist on every replica, or the secondaries will suspend.

Never, without approval

  • Shrinking other databases’ data files to free disk space. It fragments their indexes and the files simply grow again.

From my SQL Server DBA handbook.

Troubleshooting