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

SQL
8 Comments

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

SQL SERVER - Msg 1105 - Could Not Allocate Space for Object Name in Database 'DB' Because the 'PRIMARY' Filegroup is Full space-err-01

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.

SQL SERVER - Msg 1105 - Could Not Allocate Space for Object Name in Database 'DB' Because the 'PRIMARY' Filegroup is Full space-err-02-800x414

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

, , ,
Previous Post
SQL SERVER – AlwaysOn Availability Group Backup fn_hadr_backup_is_preferred_replica Not Working Correctly
Next Post
SQL SERVER – RDP Error to Azure Virtual Machines – The remote computer that you are trying to connect to requires Network Level Authentication (NLA)

Related Posts

8 Comments. Leave new

  • Pinal,

    I’m having the issue right now on one of our production DBs in SQL Server 2008. The problem is, AutoGrowth is set to 10% Unrestricted, there is plenty of room on the HD and we have plenty of other servers with similar DBs that are far greater in size, including one on this server.

    Do you have any clue what else might be limiting it?

    Thanks,

    P.S. I’m a big fan of your work and you’re one of the first places I turn to when I have an issue.

    Reply
  • I face same issues to add .ndf file.
    After that I found auto growth is restricted. I set auto growth unrestricted and issues resolved

    Reply
  • We found that modifying the local file permissions in Linux to chmod 755 %filename% was effective in relieving the error once the SQL Mgmt. settings were confirmed, SQL mgmt settings were set to auto grow @ 64MB/Unlimited.

    Reply
  • Onkar Nath T
    July 10, 2018 2:11 pm

    Can I apply the same solution for sql express? Is there any limitation on sql server express to have multiple mdf files ?

    Reply
  • Osama Hammouda
    March 7, 2019 1:59 pm

    Thank you so much for this, I would have never thought about it..you saved my Day!
    appreciate the help.

    Reply

Leave a Reply

Menu