File group is full but drive has enough space

Question

Hi Every one,

We have an error showing primary file group is full but file not set it to limited and drive has enough space. We are using standard edition and data file is occupied around 880GB. Can you please let us know if we have any clues for this.

Answer 1

Maybe it has something to do with your physical structure of your datafiles? Is there more than one datafile in that database? If yes, are they located on different disks? Maybe the mdf is located on a disk which has not enough space to extend - it contains the Metadata of your database and also still needs to extended if Metadata grows...

Maybe it has something to do with your physical structure of your disc? Fragmentation on your data disk? Filesystemtype NTFS/REFS? Blocksize?

Maybe you are using FULLTEXTSearch? Where is that one located? Is there enough space available?

It might lead to more accurate answers if you give us more information about your environment... version, disk layout, database configuration, acutal sizes and free spaces

Answer 2

This message happens when all files have 0 free space and it is unable to grow the files. This can happen for many reasons. You may have quotas on the files or user.

The SQL Server error log should log why it is out of space.

Also please post the results of this modified query (as text, not an image please).

select CAST(growth*8.0*1024/(1024*1024) AS DECIMAL(38,2)) as growthMB, 
         name,filename,
         CAST(size*8.0*1024/(1024*1024) AS DECIMAL(38,2)) as TotalMB,
         fileproperty(name,'SpaceUsed')*8.0*1024/(1024*1024) as UsedMB,
         CAST(size*8.0*1024/(1024*1024) - 
         fileproperty(name,'SpaceUsed')*8.0*1024/(1024*1024) AS DECIMAL(38,2)) as FreeMB
        ,maxsize
 from
         sysfiles 
 where
         (status & 0x2) <> 0 --and filename like ' %'

Answer 3

Hi @FG is full with available drive space ，

an error showing primary file group is full

Check the maximum allowed size of the data and the log file, if Auto Grow is not enable once the data in the file group reaches the maximum size, this error may occur

You can increase the value of the MAXSIZE parameter or set the value to UNLIMITED, by:
1.SSMS
right-click the database and click on properties

select Files and change the Autogrowth and MAXSIZE

then change Autogrowth

2.ALTER DATABASE

USE [master]  
GO  
ALTER DATABASE [DemoDatabase] MODIFY FILE ( NAME = N'database_name', MAXSIZE = UNLIMITED)  
GO  
ALTER DATABASE [DemoDatabase] MODIFY FILE ( NAME = N'database_name_log', MAXSIZE = UNLIMITED)  
GO  

Resolving SQL Server errors: The Primary Filegroup is full

both the datafiles showing 0 free space and i have cleared some space like deleting old backup tables

Your backups should not be on the same drive as your data, transaction logs and data files also have different I/O requirements, so moving to a separete dirve.
Review your backup retention, if you have backup scripts, you better monitor them

https://stackoverflow.com/questions/40592973/freeing-space-on-sql-server-drive?msclkid=bc31bc09d0d011eca6f7e56b81fd3133

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.