Table of Contents
“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.
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
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.
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.