Many users will get an error in the SQL server “The primary filegroup is full”! The administrator of the database responsibility is to look at the growth and success rate of the database carefully. Based on the utilization and growth of the database, you can plan the space management and capacity of the database connection. We used to set values for each database when creating a new one. It may be some maximum size to an unlimited one. Sometimes, we may get a problem like a constraint in hardware and business need and so, it is a must for you to set Maximum size value for each database.
Whenever you set the parameter as maximum size to a particular value at that time error “Primary filegroup is full” will be encountered. We can’t say only setting up maximum value brings this error; there is also another reason for encountering primary filegroup error is using express edition of SQL Server. 10GB is the default value of setting up the maximum size of the database. Once if you cross the 10GB without warning you will get the error “The primary filegroup is full”.
It is a must for us to understand the problem deep inside by configuring the database. Here, we are creating the database in name of “Higene”
Again, we are going to create filegrouptbl as table name. Create two columns such as name and age. Setting up the name varchar(400) and age value as char(400).
Once if you execute this query, the next step would be adding records of the demo in the table filegrouptbl. We can start inserting the row value as 4000. Just add Go 4000 in the last line.
You may get problems while executing the query:
Could not allocate space for object ‘dbo.Filegrouptbl’ in database ‘Higene’ because the ‘PRIMARY’ filegroup is full.
Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Here is the command to find the problem and occurance of error:
The output will show the details of the space occupied in the workstation. Start checking the value for Higene parameter maximum size. Using this sys.master_files query, you will be reviewing the autogrowth of the database.
12MB is the autogrowth value and 18MB is the log file. You can set 20MB as the maximum size of log files and data. Better go with the increased value of maximum size or else set the value as “Unlimited”.
Use below mentioned two methods to change the value:
Start running the query below to set the value of the Higene database up to limited.
Use sys.master_files to execute the query:
The output will show the parameter value as -1. With the help of parameter -1, you can increase the primary filegroup.
Use the below command for executing maximum size up to unlimited value:
Change the value using SQL server management studio: Start launching SQL server management studio and do connect SQL server instance. Do expansion on database and just right click on Higene and don’t forget to tap on properties.
Start changing the data files value as the maximum size. Tap on the file and check the option of the balloon in the right panel. You can see the balloon in the file type of row in maximum size and autogrowth.
You will be displayed with change autogrowth for the Higene dialogue box. Choose the “unlimited” option and then tap on the button “OK”.
You can even change the log file parameter maximum size value. Once, if you update the value of the parameter just taps on the button “OK”.
By now, your problem will be solved. So, start executing it one by one!
Whenever you set the parameter as maximum size to a particular value at that time error “Primary filegroup is full” will be encountered. We can’t say only setting up maximum value brings this error; there is also another reason for encountering primary filegroup error is using express edition of SQL Server. 10GB is the default value of setting up the maximum size of the database. Once if you cross the 10GB without warning you will get the error “The primary filegroup is full”.
It is a must for us to understand the problem deep inside by configuring the database. Here, we are creating the database in name of “Higene”
Code:
CREATE DATABASE [Higene]
ON
(NAME = N'Higene', FILENAME = N'C:\MS_SQL\Data\Higene.mdf' , SIZE = 2048KB , MAXSIZE = 20480KB )
LOG ON
(NAME = N'Higene_log', FILENAME = N'C:\MS_SQL\Log\Higene_log.ldf' , SIZE = 2048KB , MAXSIZE = 20480KB )
GO
Code:
USE [Higene]
GO
CREATE TABLE Filegrouptbl(name varchar(400), Age char(400))
GO
Code:
INSERT INTO Filegrouptbl VALUES ('Nisa','25')
GO 4000
Code:
Msg 1105, Level 17, State 2, Line 18Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Here is the command to find the problem and occurance of error:
Code:
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)}}
Code:
SELECT
databases.name AS [Database Name],
materfiles.type_desc AS [File Type],
CAST(
(materfiles.Size * 8
) / 1024.0 AS DECIMAL(18, 1)) AS [Initial Size (MB)],
'By '+IIF(
materfiles.is_percent_growth = 1, CAST(materfiles.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
(materfiles.growth * 8
) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth],
IIF(materfiles.max_size = 0, 'No growth is allowed', IIF(materfiles.max_size = -1, 'Unlimited', CAST(
(
CAST(materfiles.max_size AS BIGINT) * 8
) / 1024 AS VARCHAR(30))+' MB')) AS [MaximumSize]
FROM
sys.master_files AS materfiles
INNER JOIN sys.databases AS databases ON
databases.database_id = materfiles.database_id
where databases.name='Higene'Use below mentioned two methods to change the value:
- Alter database command
- Change value using SQL server management studio
Code:
ALTER DATABASE [DB_NAME] MODIFY FILE (LOGICAL_FILENAME, FILEGROWTH= VALUE_IN_KB|UNLIMITED)
Code:
USE [master]
GO
ALTER DATABASE [Higene MODIFY FILE ( NAME = N'Higene', MAXSIZE = UNLIMITED)
GO
ALTER DATABASE [Higene MODIFY FILE ( NAME = N'Higene_log', MAXSIZE = UNLIMITED)
GO
Code:
SELECT
databases.name AS [Database Name],
materfiles.type_desc AS [File Type],
CAST(
(materfiles.Size * 8
) / 1024.0 AS DECIMAL(18, 1)) AS [Initial Size (MB)],
'By '+IIF(
materfiles.is_percent_growth = 1, CAST(materfiles.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
(materfiles.growth * 8
) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth],
IIF(materfiles.max_size = 0, 'No growth is allowed', IIF(materfiles.max_size = -1, '-1', CAST(
(
CAST(materfiles.max_size AS BIGINT) * 8
) / 1024 AS VARCHAR(30))+' MB')) AS [MaximumSize]
FROM
sys.master_files AS materfiles
INNER JOIN sys.databases AS databases ON
databases.database_id = materfiles.database_id
where databases.name='Higene'Use the below command for executing maximum size up to unlimited value:
Code:
SET NOCOUNT ON
GO
INSERT INTO Filegrouptbl VALUES ('Nisha','25')
GO 4000Start changing the data files value as the maximum size. Tap on the file and check the option of the balloon in the right panel. You can see the balloon in the file type of row in maximum size and autogrowth.
You will be displayed with change autogrowth for the Higene dialogue box. Choose the “unlimited” option and then tap on the button “OK”.
You can even change the log file parameter maximum size value. Once, if you update the value of the parameter just taps on the button “OK”.
By now, your problem will be solved. So, start executing it one by one!
