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.

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.

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 (http://blog.sqlauthority.com)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s