Could not allocate space for object

ERROR:

“Could not allocate space for object “object_name” 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”.

If your databases are well managed you should have multiple data files for each database. It depends, of course, on database size and transaction volumes. Our environment follows a strict policy that each data file cannot be more than 30 GB, the reason being multiple data files increase the read/write performance. When any data file reaches 30GB, we get an alert, we stop auto growth on that data file and then we add a new data file.  After all is said and done, sometimes the above error message would still appear and the big question is WHY?

We have a job for Update statistics and index rebuilding. Quite often we get the above error. When I checked the database file and filegroups, they look normal, meaning auto growth is enabled and set to unlimited so why still the error.

My solution:

Check the actual file size for the database

Select *, fileproperty (name, ‘SpaceUsed’) as Used

From dbo.sysfiles

1sizeused.png

The above query identifies the data files, their size and what has been used. If the datafile have used 100%, then the amount in the ‘SIZE’ column would equal the amount in the ‘USED’ column. When update statistics or index rebuilding job runs, it might require additional space in the datafile that has reached 100% when a specific object is still in that data file. The solution;

  • mark the file which has used 100%,
  • go back to database property, under the tab ‘FILE’,
  • find the specific data file
  • increase the file size by 5 MB or 10MB2databaseproperty

Checking the file size again you will see that the ‘USED’ column of the specified data file has increased, and if you re-run the job, you should not receive an error

3usedsize

Also below query can be use to validate current data file size and available space on each file:

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

 

-By : MD ULLAH

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