SQL Server error: Primary file group is full

Question

I have a very large table in my database and I am starting to get this error

Could not allocate a new page for database 'mydatabase' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

How do you fix this error? I don't understand the suggestions there.

Answer 1

There isn't really much to add - it pretty much tells you what you need to do in the error message.

Each object (Table, SP, Index etc) you create in SQL is created on a filegroup. The default filegroup is PRIMARY. It is common to create multiple filegroups that span over many disks. For instance you could have a filegroup named INDEXES to store all of your Indexes. Or if you have one very large table you could move this on to a different filegroup.

You can allocate space to a filegroup, say 2GB. If Auto Grow is not enabled once the data in the filegroup reaches 2GB SQL Server cannot create any more objects. This will also occur is the disk that the filegroup resides on runs out of space.

I'm not really sure what else to add - as I said previously, the error message pretty much tells you what is required.

Answer 2

If you're using SQL Express you may be hitting the maximum database size limit (or more accurately the filegroup size limit) which is 4GB for versions up to 2005, 10GB for SQL Express 2008 onwards. That size limit excludes the log file.

Answer 3

If you are using client tools (MSDE) then the data in the filegroup reaches 2GB, SQL Server cannot create any more objects.