SQL SERVER – Msg 1105 – Could Not Allocate Space for Object Name in Database ‘DB’ Because the ‘PRIMARY’ Filegroup is Full

This is one of the famous error which has been reported many times by many clients. Here is the exact error which you might see. One of my clients complained that our database was unable to grow despite available space on the disk. Here is the error which they shared. Let us learn about error related to allocate space.

Could not allocate space for object ‘TableName’ in database ‘DB’ 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 are the steps to reproduce the issue. Make sure you have a folder called Temp in C drive.

CREATE DATABASE [SQLAuthDB]
 ON   
(NAME = N'SQLAuthDB', FILENAME = N'C:\Temp\SQLAuthDB.mdf' , SIZE = 8192KB , MAXSIZE = 10240KB )
 LOG ON 
(NAME = N'SQLAuthDB_log', FILENAME = N'C:\Temp\SQLAuthDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 10240KB )
GO

Once database is created, you can run below script to reproduce the error.

Here is the text of the message

USE [SQLAuthDB]
GO
CREATE TABLE ThisTable(i int, j char(8000))
GO
SET NOCOUNT ON
GO
INSERT INTO ThisTable VALUES (1,'SQLAuthority')
GO 961

Please note that there is digit 961 after the GO. It is not an error. It means the previous statement will run 961 times.

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

Now, let look at database properties and you would find the answer.

WORKAROUND/SOLUTION

Whenever you get such error check below.

1. Check if auto growth is restricted.
2. Check if auto growth is set to a high value which would cause disk space to become full.
3. Check if disk space is low.
4. Check SQL edition because SQL Express has a database file size limit.

Based on any of the above situation, you need to take corrective action. Here is the command to set the growth to UNLIMITED.
[sq]USE [master] GO
ALTER DATABASE

[SQLAuthDB] MODIFY FILE (NAME = N’SQLAuthDB’, MAXSIZE = UNLIMITED)
GO[/sql]

Cleanup of demo

Below are the steps to clean up the demo database created earlier.

USE [master]
GO
ALTER DATABASE [SQLAuthDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SQLAuthDB]
GO

Have you faced a similar error? What was the solution you implemented?

Reference: Pinal Dave (https://blog.sqlauthority.com)