question

FGisfullwithavailabledrivespace-1981 avatar image
0 Votes"
FGisfullwithavailabledrivespace-1981 asked TomPhillips-1744 commented

File group is full but drive has enough space

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.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BjoernPeters avatar image
0 Votes"
BjoernPeters answered FGisfullwithavailabledrivespace-1981 commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Peters,

thanks for your reply.
we are using 2014 SQL standard edition and have two more data files on same drive and the last datafile i have created to resolve the issue. we have only single drive and drive has enough space. Below are spaces of the datafiles.

[1]: /answers/storage/attachments/200624-image.png

[2]: /answers/storage/attachments/200651-capture.jpg

0 Votes 0 ·
image.png (5.1 KiB)
capture.jpg (19.7 KiB)
BjoernPeters avatar image BjoernPeters FGisfullwithavailabledrivespace-1981 ·

Can you please share the result of the following query:

 use [dbname]
 select growth, 
         name,filename,
         size*8.0*1024/(1024*1024) as TotalMB,
         fileproperty(name,'SpaceUsed')*8.0*1024/(1024*1024) as UsedMB,
         size*8.0*1024/(1024*1024) - 
         fileproperty(name,'SpaceUsed')*8.0*1024/(1024*1024) as FreeMB
 from
         sysfiles 
 where
         (status & 0x2) <> 0 --and filename like ' %'

Thank you

0 Votes 0 ·

Sure. When we have a issue both the datafiles showing 0 free space and i have cleared some space like deleting old backup tables and what i could not understand is why it is not getting space from drive. Could you please explain


[1]: /answers/storage/attachments/200634-capture1.jpg

0 Votes 0 ·
capture1.jpg (43.3 KiB)
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

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 ' %'
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.



growthMB filename TotalMB UsedMB FreeMB maxsize
512.00 D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA.mdf 899760.13 898541.375000000000 1218.75 -1
512.00 D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\1.ndf 472856.00 453450.750000000000 19405.25 -1
500.00 D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\2.ndf 25000.00 13361.125000000000 11638.88 -1

0 Votes 0 ·
TomPhillips-1744 avatar image TomPhillips-1744 FGisfullwithavailabledrivespace-1981 ·

The SQL Server error log will indicate why it was not able to grow the file. Please check the error log.

0 Votes 0 ·
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @FGisfullwithavailabledrivespace-1981


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
200757-1.png

select Files and change the Autogrowth and MAXSIZE
200812-2.png

then change Autogrowth
200821-3.png

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.








1.png (10.4 KiB)
2.png (18.9 KiB)
3.png (13.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

All the files has in unlimited only

0 Votes 0 ·
Yufeishao-msft avatar image Yufeishao-msft FGisfullwithavailabledrivespace-1981 ·

you can try to increase file size growth, in MB, 512MB does not seem be appropriate, or try to shrink your db file
defrag the disk where the data file located

0 Votes 0 ·