SQL SERVER – Error – Resolution – Could not allocate space for object in database because the filegroup is full

Just a day ago on my local box I received following error.

Could not allocate space for object ‘<object_name>’ in database ‘<database_name>’ 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.

I was a bit surprised as this was not a production server or even real server. It was my laptop with SQL Server installed in it for testing and presentation purpose. I was a bit shocked and decided to figure out what is wrong with my SQL Server. Just like everyone I searched online and I end up having few solutions, however, after a while I realized then what exactly I was missing. However, I encourage all of you to read various steps I took to reach to a solution to this problem.

Solution / Fix :

Step 1: I checked if my Hard Drive has more space available.

Solarwinds

If your Hard Drive is full, please empty out any unnecessary stuff. This may not directly fix your problem, but it is a necessary exercise to do before you do Step 2.

Step 2: Delete unnecessary object from your file group.

If your primary filegroup (in most cases) or filegroup which has generated this error is restricted to fix size, you may get this error. You have two workarounds here.

SQL SERVER - Error - Resolution - Could not allocate space for object in database because the filegroup is full fixedgrowth

Step 2a: Delete objects from your filegroup which you do not need. You can move objects to another filegroup if you have multiple filegroups.

Step 2b: Increase the fixed size of your primary filegroup.

Step 3: Check your SQL Server version.

SQL Server Express version 2008 and earlier had the limitation of 4GB maximum Database size. SQL Server Express 2008 R2 and onwards have limitation 10GB maximum database size.

In my case, I was running SQL Server Express version, hence I had faced above error. I moved my database to standard edition and everything went fine.

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

Solarwinds
Previous Post
SQL SERVER – SSIS Data Flow Troubleshooting – Part1 – Notes from the Field #019
Next Post
SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Related Posts

15 Comments. Leave new

  • We are using SQL 2005 and have the same error message. I have the option to AutoGrowth and set File size to Unlimited and have selected to increase by 10 MG.. Do you have any other suggestion??

    Reply
    • If you use SQL Server 2005 EXPRESS => Express version has a licensing limit of about 10gb.
      So even though the size was set to “unlimited”, it wasn’t.

      Pinal, you can specify in your post.
      Great post, good job.

      Reply
  • Hello Pinal,
    Thanks for your clear explanations.
    Although I’d like to know if a “shrinkfile” would be as efficient.
    Thank you,

    Mat

    Reply
  • (I forgot to mention that I have SQL Server 2012)*
    Thanks again

    Reply
  • If we have SQL Server 2005 Express Merge Replication Subscriptions that synch to a SQL Server 2008 R2 Standard Edition. Do the Subscriptions still have same file size limitations even though they synch to a Proper Standard Version of SQL Server 2008?

    Reply
  • Great post Pinal! This is why I like your blog; good no frills explanations that describes both the root cause and the solution. Plus there is no snobbery and people don’t get scolded for not “putting enough effort into asking questions” like they do on sites like [].

    Keep up the good work!

    Reply
  • Hi,
    I want to bulk insert into my database from flat text file, which size is more than 12GB.
    I’ve already configure Rows data and Log files to unlimited with auto growth, Also available space in my drive. But during insertion i found this error-
    “Msg 1105, Level 17, State 2, Line 1
    Could not allocate space for object ‘TABLE’ in database ‘DATABASENAME’ 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.”
    I found these when .mdf file is almost 10GB and .ldf file is 23 GB.
    Most probably there is a 10GB database size limitations. But I’ve to import it.
    What will be the process..?
    Please help me..
    Thanks in advance..
    -Jewel

    Reply
  • Gr8 Sir. it works and save my time .

    Reply
  • Pinal, hi. Based on step 2a, could you please advice how i can understand what tables i can delete from SQL DB to reduce it size? I am not developer and i am afraid to delete some necessary. Thanks in advance.

    Reply
  • Cara, obrigado.

    Sem palavras, resolveu meu problema de dias

    Reply
  • Bhaskar Reddy G
    March 12, 2017 7:52 pm

    I have one server. on that I usually gets ‘file group running out of space’ alert . when I checked the issue, the related file group don’t exist. every time this is repeating. Please provide solution for this

    Reply
  • Ricardo Fugimoto
    April 18, 2017 7:59 pm

    Great!! Fixed my problem

    Reply

Leave a Reply

Menu