When running schema changes or creating indexes in SQL Server, you may encounter an error like this:
Could not allocate a new page for database because the 'PRIMARY'
filegroup is full due to lack of storage space or database files
reaching the maximum allowed size.This is not a tool-specific problem. It means SQL Server attempted to allocate space in the PRIMARY filegroup but could not grow the data file. Until the underlying space issue is fixed, operations such as index creation, schema synchronization, or data inserts may fail.
This guide covers the common causes, diagnostic queries, fixes, and proactive monitoring.
Common causes
- The drive hosting the data file has run out of space.
- Autogrowth is set too small (e.g., 64 MB) and repeated growth fails when the drive is tight.
- The file has a
MAXSIZEcap or a quota is in place. - There is only one data file in PRIMARY and it cannot grow.
- Large operations (like index builds) require more space than anticipated.
Step 1: Diagnose the problem
Check file space inside the database
SELECT name AS logical_name,
type_desc,
size/128.0 AS size_mb,
FILEPROPERTY(name, 'SpaceUsed')/128.0 AS used_mb,
(size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS free_mb,
growth,
is_percent_growth,
max_size
FROM sys.database_files;Interpretation: if free_mb is close to zero, SQL will attempt to auto-grow the file.
Check free space on the hosting volume
SELECT DISTINCT
vs.volume_mount_point,
CAST(vs.total_bytes/1024.0/1024/1024 AS DECIMAL(10,1)) AS total_gb,
CAST(vs.available_bytes/1024.0/1024/1024 AS DECIMAL(10,1)) AS free_gb
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point;If free_gb is very low, the drive is the bottleneck.
Check growth and size limits
SELECT mf.name,
mf.physical_name,
mf.size/128.0 AS size_mb,
CASE WHEN mf.is_percent_growth = 1
THEN CONCAT(mf.growth, '%')
ELSE CONCAT(mf.growth/128, ' MB') END AS filegrowth,
CASE WHEN mf.max_size = -1 THEN 'Unlimited'
ELSE CONCAT(mf.max_size/128, ' MB') END AS max_size
FROM sys.master_files AS mf;Step 2: Fix options
Option A: Free or extend space on the data drive
- Clean up space or extend the LUN.
- Pre-grow the file to reduce auto-growth events:
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = YourDataFile, SIZE = 60GB);
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = YourDataFile, FILEGROWTH = 1024MB);Option B: Add a second data file
If another drive has room, spread growth across multiple files:
ALTER DATABASE [YourDatabase]
ADD FILE (
NAME = YourDataFile2,
FILENAME = 'E:\SQLData\YourDataFile2.ndf',
SIZE = 20GB,
FILEGROWTH = 1024MB
);Option C: Create a new filegroup
Keep PRIMARY small by directing large objects elsewhere:
ALTER DATABASE [YourDatabase] ADD FILEGROUP FG_UserData;
ALTER DATABASE [YourDatabase]
ADD FILE (NAME = UserDataFile1,
FILENAME = 'E:\SQLData\UserDataFile1.ndf',
SIZE = 20GB, FILEGROWTH = 1024MB)
TO FILEGROUP FG_UserData;
ALTER DATABASE [YourDatabase] MODIFY FILEGROUP FG_UserData DEFAULT;Option D: Optimize the operation
Use SORT_IN_TEMPDB = ON if tempdb has room to reduce pressure on PRIMARY:
CREATE NONCLUSTERED INDEX IX_Sample
ON dbo.YourTable (YourColumn)
WITH (SORT_IN_TEMPDB = ON);Step 3: Proactive Monitoring
Proactive monitoring prevents downtime. Below are two approaches:
A) Drive Space Monitoring with SQL Agent
This script checks drive space using PowerShell and xp_cmdshell. It can be scheduled as a SQL Agent job and sends an email if any drive is below 10% free space
-- Enable xp_cmdshell temporarily
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;
DECLARE @svrName VARCHAR(255);
DECLARE @sql VARCHAR(400);
SET @svrName = @@SERVERNAME;
SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') +
' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';
CREATE TABLE #output (line VARCHAR(255));
INSERT #output EXEC xp_cmdshell @sql;
CREATE TABLE ##DriveSpace
(
ServerName SYSNAME,
[PhysicalName] SYSNAME,
[Capacity(GB)] NVARCHAR(20),
[Drive FreeSpace(GB)] NVARCHAR(20)
);
INSERT INTO ##DriveSpace
SELECT @@SERVERNAME,
RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX('|',line) -1))) AS PhysicalName,
ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) AS FLOAT)/1024,0),
ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) AS FLOAT)/1024,0)
FROM #output
WHERE line LIKE '[A-Z][:]%';
DROP TABLE #output;
-- Send alert if free space < 10%
IF EXISTS (SELECT * FROM ##DriveSpace WHERE (CAST([Drive FreeSpace(GB)] AS FLOAT) / CAST([Capacity(GB)] AS FLOAT)) < 0.10)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='YourMailProfile',
@recipients= 'dba_team@example.com',
@subject = 'Drive Free Space Alert',
@body = 'Warning: One or more drives are below 10% free space.',
@body_format = 'HTML';
END
DROP TABLE ##DriveSpace;
-- Disable xp_cmdshell after use
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;B) Database File & Space Monitoring
This script gives detailed usage for data and log files across all databases (or a target DB). It can be scheduled daily to track trends in a reporting table.
Parameters:
@TargetDatabase = NULL→ all DBs, or specify one.@Level = 'File'or'Database'.@Unit = 'MB','GB', or'KB'.@UpdateUsage = 1→ runs DBCC UPDATEUSAGE for accuracy.
DECLARE
@TargetDatabase sysname,
@Level varchar(10),
@UpdateUsage bit,
@Unit char(2),
@SQL VARCHAR(8000)
Select
@TargetDatabase = NULL, -- NULL: all dbs; DBName: Database
@Level = 'File', -- 'Database' or 'File'
@UpdateUsage = 0, -- 0 no update; 1 update
@Unit = 'MB' -- Megabytes (MB), Kilobytes (KB) or Gigabytes (GB)
IF @TargetDatabase IS NOT NULL AND DB_ID(@TargetDatabase) IS NULL
BEGIN
RAISERROR(15010, -1, -1, @TargetDatabase);
-- RETURN (-1)
END
IF OBJECT_ID('tempdb.dbo.#Tbl_CombinedInfo', 'U') IS NOT NULL
DROP TABLE dbo.#Tbl_CombinedInfo;
IF OBJECT_ID('tempdb.dbo.##Tbl_DbFileStats', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_DbFileStats;
IF OBJECT_ID('tempdb.dbo.##Tbl_ValidDbs', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_ValidDbs;
IF OBJECT_ID('tempdb.dbo.##Tbl_Logs', 'U') IS NOT NULL
DROP TABLE dbo.##Tbl_Logs;
CREATE TABLE dbo.#Tbl_CombinedInfo (
DatabaseName sysname NULL,
[type] VARCHAR(10) NULL,
[FileGroup] VARCHAR(45) NULL,
LogicalName sysname NULL,
T dec(10, 2) NULL,
U dec(10, 2) NULL,
[U(%)] dec(5, 2) NULL,
F dec(10, 2) NULL,
[F(%)] dec(5, 2) NULL,
PhysicalName sysname NULL );
CREATE TABLE dbo.##Tbl_DbFileStats (
Id int identity,
DatabaseName sysname NULL,
FileId int NULL,
FileGroup int NULL,
TotalExtents bigint NULL,
UsedExtents bigint NULL,
Name sysname NULL,
FileName varchar(255) NULL );
CREATE TABLE dbo.##Tbl_ValidDbs (
Id int identity,
Dbname sysname NULL );
CREATE TABLE dbo.##Tbl_Logs (
DatabaseName sysname NULL,
LogSize dec (10, 2) NULL,
LogSpaceUsedPercent dec (5, 2) NULL,
Status int NULL );
DECLARE @Ver varchar(10),
@DatabaseName sysname,
@Ident_last int,
@String varchar(2000),
@BaseString varchar(2000);
SELECT @DatabaseName = '',
@Ident_last = 0,
@String = '',
@Ver = CASE WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005'
WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000'
WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008'
WHEN @@VERSION LIKE '%11.0%' THEN 'SQL 2012'
WHEN @@VERSION LIKE '%12.0%' THEN 'SQL 2014'
WHEN @@VERSION LIKE '%13.0%' THEN 'SQL 2016'
WHEN @@VERSION LIKE '%14.0%' THEN 'SQL 2017'
WHEN @@VERSION LIKE '%15.0%' THEN 'SQL 2019'
WHEN @@VERSION LIKE '%16.0%' THEN 'SQL 2022'
END;
SELECT @BaseString =
' SELECT DB_NAME(), ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN sf.status & 0x40 = 0x40 THEN ''Log'' ELSE ''Data'' END'
ELSE ' CASE sf.type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END +
', sf.name, ' +
CASE WHEN @Ver = 'SQL 2000' THEN ' sf.filename, ' ELSE ' sf.physical_name, ' END +
CASE WHEN @Ver = 'SQL 2000' THEN 'sfg.groupname,' ELSE 'sfg.name,' END +
' sf.size*8.0/1024.0 FROM ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles sf LEFT OUTER JOIN sysfilegroups sfg ON sf.groupid = sfg.groupid' ELSE 'sys.database_files sf LEFT OUTER JOIN sys.filegroups sfg ON sf.data_space_id = sfg.data_space_id' END +
' WHERE '
+ CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'sf.state_desc = ''ONLINE''' END + '';
SELECT @String = 'INSERT INTO dbo.##Tbl_ValidDbs SELECT name FROM ' +
CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases'
WHEN @Ver IN ('SQL 2005', 'SQL 2008','SQL 2012','SQL 2014') THEN 'master.sys.databases'
END + ' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC';
EXEC (@String);
INSERT INTO dbo.##Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');
-- For data part
IF @TargetDatabase IS NOT NULL
BEGIN
SELECT @DatabaseName = @TargetDatabase;
IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName,'Status') = 'ONLINE'
AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
BEGIN
SELECT @String = 'USE [' + @DatabaseName + '] DBCC UPDATEUSAGE (0)';
PRINT '*** ' + @String + ' *** ';
EXEC (@String);
PRINT '';
END
SELECT @String = 'INSERT INTO dbo.#Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, FileGroup, T) ' + @BaseString;
INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
EXEC ('USE [' + @DatabaseName + '] ' + @String);
UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName;
END
ELSE
BEGIN
WHILE 1 = 1
BEGIN
SELECT TOP 1 @DatabaseName = Dbname FROM dbo.##Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC;
IF @@ROWCOUNT = 0
BREAK;
IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName, 'Status') = 'ONLINE'
AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'
BEGIN
SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') ';
PRINT '*** ' + @String + '*** ';
EXEC (@String);
PRINT '';
END
SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.##Tbl_DbFileStats;
SELECT @String = 'INSERT INTO dbo.#Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, FileGroup, T) ' + @BaseString;
EXEC ('USE [' + @DatabaseName + '] ' + @String);
INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');
UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY;
END
END
-- set used size for data files, do not change total obtained from sys.database_files as it has for log files
UPDATE dbo.#Tbl_CombinedInfo
SET U = s.UsedExtents*8*8/1024.0
FROM dbo.#Tbl_CombinedInfo t JOIN dbo.##Tbl_DbFileStats s
ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName;
-- set used size and % values for log files:
UPDATE dbo.#Tbl_CombinedInfo
SET [U(%)] = LogSpaceUsedPercent,
U = T * LogSpaceUsedPercent/100.0
FROM dbo.#Tbl_CombinedInfo t JOIN dbo.##Tbl_Logs l
ON l.DatabaseName = t.DatabaseName
WHERE t.type = 'Log';
UPDATE dbo.#Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T;
UPDATE dbo.#Tbl_CombinedInfo SET [F(%)] = F*100.0/T;
IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'
BEGIN
IF @Unit = 'KB'
UPDATE dbo.#Tbl_CombinedInfo
SET T = T * 1024, U = U * 1024, F = F * 1024;
IF @Unit = 'GB'
UPDATE dbo.#Tbl_CombinedInfo
SET T = T / 1024, U = U / 1024, F = F / 1024;
END
IF UPPER(ISNULL(@Level, 'DATABASE')) = 'DATABASE'
BEGIN
DECLARE @Tbl_Final TABLE (
DatabaseName sysname NULL,
TOTAL dec (10, 2),
[=] char(1),
used dec (10, 2),
[used (%)] dec (5, 2),
[+] char(1),
free dec (10, 2),
[free (%)] dec (5, 2),
[==] char(2),
Data dec (10, 2),
Data_Used dec (10, 2),
[Data_Used (%)] dec (5, 2),
Data_Free dec (10, 2),
[Data_Free (%)] dec (5, 2),
[++] char(2),
Log dec (10, 2),
Log_Used dec (10, 2),
[Log_Used (%)] dec (5, 2),
Log_Free dec (10, 2),
[Log_Free (%)] dec (5, 2) );
INSERT INTO @Tbl_Final
SELECT x.DatabaseName,
x.Data + y.Log AS 'TOTAL',
'=' AS '=',
x.Data_Used + y.Log_Used AS 'U',
(x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log) AS 'U(%)',
'+' AS '+',
x.Data_Free + y.Log_Free AS 'F',
(x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log) AS 'F(%)',
'==' AS '==',
x.Data,
x.Data_Used,
x.Data_Used*100/x.Data AS 'D_U(%)',
x.Data_Free,
x.Data_Free*100/x.Data AS 'D_F(%)',
'++' AS '++',
y.Log,
y.Log_Used,
y.Log_Used*100/y.Log AS 'L_U(%)',
y.Log_Free,
y.Log_Free*100/y.Log AS 'L_F(%)'
FROM
( SELECT d.DatabaseName,
SUM(d.T) AS 'Data',
SUM(d.U) AS 'Data_Used',
SUM(d.F) AS 'Data_Free'
FROM dbo.#Tbl_CombinedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x
JOIN
( SELECT l.DatabaseName,
SUM(l.T) AS 'Log',
SUM(l.U) AS 'Log_Used',
SUM(l.F) AS 'Log_Free'
FROM dbo.#Tbl_CombinedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y
ON x.DatabaseName = y.DatabaseName;
IF @Unit = 'KB'
UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024,
used = used * 1024,
free = free * 1024,
Data = Data * 1024,
Data_Used = Data_Used * 1024,
Data_Free = Data_Free * 1024,
Log = Log * 1024,
Log_Used = Log_Used * 1024,
Log_Free = Log_Free * 1024;
IF @Unit = 'GB'
UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024,
used = used / 1024,
free = free / 1024,
Data = Data / 1024,
Data_Used = Data_Used / 1024,
Data_Free = Data_Free / 1024,
Log = Log / 1024,
Log_Used = Log_Used / 1024,
Log_Free = Log_Free / 1024;
DECLARE @GrantTotal dec(11, 2);
SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final;
SELECT
CONVERT(dec(10, 2), TOTAL*100.0/@GrantTotal) AS 'WEIGHT (%)',
DatabaseName AS 'DATABASE',
CONVERT(VARCHAR(12), used) + ' (' + CONVERT(VARCHAR(12), [used (%)]) + ' %)' AS 'USED (%)',
[+],
CONVERT(VARCHAR(12), free) + ' (' + CONVERT(VARCHAR(12), [free (%)]) + ' %)' AS 'FREE (%)',
[=],
TOTAL,
[=],
CONVERT(VARCHAR(12), Data) + ' (' + CONVERT(VARCHAR(12), Data_Used) + ', ' +
CONVERT(VARCHAR(12), [Data_Used (%)]) + '%)' AS 'DATA (used, %)',
[+],
CONVERT(VARCHAR(12), Log) + ' (' + CONVERT(VARCHAR(12), Log_Used) + ', ' +
CONVERT(VARCHAR(12), [Log_Used (%)]) + '%)' AS 'LOG (used, %)'
FROM @Tbl_Final
WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')
ORDER BY DatabaseName ASC;
IF @TargetDatabase IS NULL
SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',
SUM (used) AS 'USED',
SUM (free) AS 'FREE',
SUM (TOTAL) AS 'TOTAL',
SUM (Data) AS 'DATA',
SUM (Log) AS 'LOG'
FROM @Tbl_Final;
END
Select
@@Servername as Servername
,DatabaseName as DatabaseName
,[Type]
,[FileGroup]
,LogicalName
,T as [File Size]
,U as [Used Space]
,[U(%)] as [Used Percent]
,F as [Free Space]
,[F(%)] as [Percent Free]
,PhysicalName
,CAST(CONVERT(CHAR(10), GETDATE(), 101) AS smalldatetime) as poll_date
From dbo.#Tbl_CombinedInfo --where [F(%)] < 10.00
-- This script:
-- 1. Loops through databases
-- 2. Uses DBCC SHOWFILESTATS + DBCC SQLPERF(LOGSPACE)
-- 3. Aggregates data/log usage into a combined report
-- 4. Outputs either file-level or DB-level summary
The script automatically cleans prior runs, keeps 1 year of history (configurable), and lets you trend growth over time.
Summary
The error “could not allocate a new page” means SQL Server couldn’t extend the PRIMARY filegroup. Fixes include:
- Freeing disk space,
- Adjusting file growth settings,
- Adding new files or filegroups,
- Using tempdb for sort operations.
Proactive monitoring is key — drive space checks + database file reports ensure DBAs get early warnings before failures hit production workloads.
Final Thoughts
Storage growth should be planned, not reactive. By combining:
- Diagnostic queries,
- Drive-level monitoring (alerts when free space <10%), and
- Database file monitoring (daily trending reports),
you’ll build a resilient monitoring process that prevents outages during schema syncs, index rebuilds, or heavy inserts.
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
