How to fix Error 1922 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 1922 — “Filegroup has no files assigned to it”

Why This Error Happens

This error occurs when a filegroup is created or referenced in a database but no data files (.ndf/.mdf) have been explicitly assigned to it before an attempt is made to create a table, index, or insert data into that filegroup. SQL Server 2025 requires at least one physical file to be associated with a filegroup before it can store any data, since the filegroup itself is only a logical container. This typically happens due to incomplete migration scripts, manual filegroup creation without a corresponding ADD FILE statement, or a failed/partial restore operation that dropped file references.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Filegroup and Database

  • Run the following query to list all filegroups and check which ones have zero files:
SELECT fg.name AS FilegroupName, f.name AS FileName, f.physical_name
FROM sys.filegroups fg
LEFT JOIN sys.master_files f ON fg.data_space_id = f.data_space_id
WHERE f.name IS NULL;
  • Confirm the exact filegroup name reported in Error 1922 by cross-referencing the error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Add a Physical File to the Filegroup

  • Use ALTER DATABASE to add a file to the empty filegroup. Replace paths and names as needed:
ALTER DATABASE [YourDatabase]
ADD FILE (
    NAME = N'YourFilegroup_Data1',
    FILENAME = N'C:\SQLData\YourDatabase_Filegroup1.ndf',
    SIZE = 64MB,
    FILEGROWTH = 64MB
) TO FILEGROUP [YourFilegroup];
  • Ensure the target directory (e.g., C:\SQLData\) exists and that the SQL Server service account has Write and Modify NTFS permissions on that folder.

Step 3: Validate Disk Space and Service Account Permissions

  • Open Server Manager > Disk Management on Windows Server 2025 to confirm the target volume has sufficient free space.
  • Check Local Security Policy > User Rights Assignment to ensure the SQL Server service account has “Log on as a service” and file system rights to the data directory.
  • Restart the SQL Server service via services.msc or run:
Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Re-run the Failed Operation

  • Re-execute the original CREATE TABLE or CREATE INDEX statement that triggered Error 1922.
  • Confirm no other filegroups in the same database are similarly empty by re-running the Step 1 diagnostic query.

Alternative or Verification Steps

  • Verify the fix by running sp_helpfile or querying sys.database_files to confirm the new file appears under the correct filegroup with an ONLINE state.
  • If the filegroup was created in error (e.g., via a migration script typo), an alternative fix is to drop the empty filegroup entirely using ALTER DATABASE [YourDatabase] REMOVE FILEGROUP [YourFilegroup]; and redirect the table/index creation to an existing, valid filegroup such as PRIMARY.