Could not allocate space for object dbo.table

Question

The number of rows in the table is only 601 records. Looking in the database I cannot see any problems: the initial size is set to 4Gb, autogrowth is set by 1Mb (I then set it to 10%) but this did not make any difference.

Could not allocate space for object 'dbo.Fatawa'.'PK_table' in database 'database' 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.

Answer 1

The error message is telling you that the PRIMARY filegroup (FG) is full. In SQL Server Management Studio (SSMS), right click the database and selection properties.

On the files table, find the primary data file (*.mdf) and write down the location (drive, path, file).

Go to Windows explorer, click the my computer icon, notice the drives. Each drive will have total space and amount available.

Problem:

Is there any space left on the drive? If not, that is your problem.

Solution:

If there is space left in the data file, shrink the file. If not, create a secondary data file. Move some tables to the data file. Shrink the primary data file after move is complete.

Here is a script from microsoft by Roberto Stefanetti. It will move a table and indexes from one FG to another.

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f

Answer 2

i used the stored procedure:

sp_helpdb (DB name)

this showed me the growth size was too small so i changed it like this:

  ALTER DATABASE (DB name)
  MODIFY FILE
  (NAME=(DB name),FILEGROWTH=20MB);

Answer 3

In my case I use SQL Server 2005 EXPRESS version and this have database limitation to 4 GB max size.

I get this error:

Could not allocate space for object 'dbo.SORT temporary run storage:440737612283904' in database 'LargeDB' 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...

To solve this problem you need to upgrade to SQL Server version 2008 R2 Express Database Size Limit Increased to 10GB, or upgrade your license.