In this article, we are going to understand SQL Server error: The Primary filegroup is full and how we can fix this error.

The database administrators must keep track of the database growth. Also, they must do the capacity planning and space management according to the growth and utilization 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 due to business requirements and hardware constraints, we are forced to keep the value of the MAXSIZE 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. This error also occurs when we are using the SQL Server express edition. 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 understand the concept and error, we must create a demo setup with a specific configuration. I have created a database named DemoDatabase, and the size of the database is 40MB. Run the following query to create the database.

In the database, we are creating a table named tblFilgroup in the DemoDatabase. The table contains two columns named first_name and address. The datatype of the first_name column is varchar(500) and the datatype of the address column is char(8000). Execute the following query to create the tblFilgroup table.

Once the table is created, we are inserting some demo records in the tblFilgroup table. We are inserting 5000 rows in the table. To insert 5000 records, we can use GO 5000 keywords at the end of the insert query. Execute the following query to insert the records.

During the execution of the INSERT query, we encounter the following error.

Msg 1105, Level 17, State 2, Line 18
Could not allocate space for object ‘dbo.tblFilegroup’ in database ‘DemoDatabase’ 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.

Screenshot:

To identify the root cause, first, let us check the available disk space in the workstation. Run the following command in the PowerShell.

Command Output:

As you can see in the above image, sufficient space is available in the workstation. Now, let us see the value of the MAXSIZE parameter of DemoDatabase. We can query the sys.master_files DMV to review the Autogrowth and max size values of the DemoDatabase. Run the following query.

Output

As you can in the above image, the value of the Autogrowth parameter for the data file is 12MB and the log file is 18MB. The maximum allowed size of the data and log file is set to 20MB. To resolve this issue, we can increase the value of the MAXSIZE parameter or set the value to UNLIMITED. In our case, we are going to set the value of the MAXSIZE parameter to UNLIMITED. We can change the values by using any of the following methods.

1. Change value using SQL Server Management Studio (SSMS)
2. ALTER DATABASE command

First, let us change the value using SQL Server management studio. Launch SQL Server Management Studio -> Connect to the SQL Server instance -> Expand Databases Right-click on DemoDatabase and click on Properties.

First, we will change the MAXSIZE of data files. To do that, click on Files. In the right pane, click on a balloon in the Autogrowth / Maxsize column in ROWS File Type.

A dialog box named Change Autogrowth for DemoDatabase opens. In the dialog box, set Maximum File Size to Unlimited. Click on OK to save the value and close the dialog box.

Similarly, change the value of the MAXSIZE parameter of the log files. Once parameter values are updated, click OK to close the database properties.

Alternatively, we can use the ALTER DATABASE statement to change the values of the MAXSIZE parameter. To change the MAXSIZE parameter, the syntax of the ALTER DATABASE statement is the following.

We want to set the value MAXSIZE parameter of the data and log files of the Demodatabase to UNLIMITED. To do that, run the following query.

Once the query is executed successfully, run the below query against sys.master_files DMV.

Output:

As you can see, the value of the MAXSIZE parameter is -1. According to the Microsoft document, when the MAXSIZE parameter value is -1, the data file can grow until the disk is full.

Now, run the following INSERT statement to insert 5000 records. As we have set the value of the MAXSIZE parameter to UNLIMITED, the records will be inserted successfully.

Output.

As you can see in the above image, the primary filegroup is full error has been resolved and the query inserted 5000 records in the tblFilgroup table successfully.

Summary

In this article, I have explained about the primary filegroup is full error and how to resolve the error. I have also demonstrated a use case to explain when this error can occur and how to fix it and make the database operational.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022