Home » SQL Server errors: The Primary Filegroup is full

SQL Server errors: The Primary Filegroup is full

SQL Server errors: The Primary Filegroup is full

“sql server error 1105 the ‘primary’ filegroup is full”  or ” Event ID: 1105 MSSQL$SQLEXPRESS – primary filegroup is full.”  error fix is provided in the article.

primary filegroup is full sql server 2014 , needs Primary group to be extended because the ‘primary’ filegroup is full sql server 2014 is error when primary group does not have any additional space for storing data or maximum file size is reached
The error sql server database error ‘primary’ filegroup is full is more common in SQL Express editions as it has maximum size limit.
The DBA’s always need to keep track of the database growth. Also, they must do the capacity planning and space management for the growth and utilisation of the database. When we create a new database, we set the value of the max size parameter to UNLIMITED most of the time. However, sometimes we keep it limited as per business SOP’s or needs MAXSIZE (maximum size set on file) parameter to a specific value.

When we set the value of the MAXSIZE parameter limited to a specific value, we might encounter the Primary filegroup is full error message when full. This error is more common in SQL Server express edition where The maximum allowed size of the database in SQL Server Express edition is 10GB. When the database tries to grow beyond 10GB, the user will encounter the Primary filegroup is full error.

To identify the root cause, first, let us check the available disk space on server manually or Run the following command in the PowerShell.

Get-WmiObject -Class Win32_logicaldisk -Filter "DriveType = '3'" | Select-Object -Property DeviceID, DriveType, VolumeName, @{L='AvailableSpace';E={"{0:N2}" -f ($_.FreeSpace /1GB)}}, @{L="TotalDiskSize";E={"{0:N2}" -f ($_.Size/1GB)}}

when sufficient space is available on the server then we can increase MAXSIZE parameter

Below query for Database setting from DMV’s
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 ' %'

To resolve this issue, we can increase the value of the MAXSIZE parameter or set the value to UNLIMITED.

Change value of MAXSIZE using SQL Server Management Studio (SSMS)
ALTER DATABASE command

  1. click on Properties.
  2. Open database property by right click on database
  3. change the MAXSIZE of data files as needed on Files. We can set it to UNLIMITED if we like.

Alternatively, we can use the ALTER DATABASE statement to change the values of the MAXSIZE parameter with command below:
ALTER DATABASE [DB_NAME] MODIFY FILE (LOGICAL_FILENAME, FILEGROWTH= VALUE_IN_KB)
ALTER DATABASE [DB_NAME] MODIFY FILE (LOGICAL_FILENAME, FILEGROWTH= UNLIMITED)

Now check for changes

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 ' %'

In Simple words
1 check if Hard Drive has more space available. If  Hard Drive is full then delete or clear any unnecessary files.

2 Delete unnecessary object from your file group to free up file group if possible

3 Increase the file size which is fixed size if its restricted of your primary filegroup.

4: Check your SQL Server version if max size is restricted for filegroup.

SQL Server Express version 2008 and earlier had the limitation of 4GB maximum Database size. SQL Server Express 2008 R2 and 2012 upwards have limitation 10GB maximum database size which is restriction for free SQL express edition.

Leave a Reply

Your email address will not be published. Required fields are marked *