Insufficient disk space in Filegroup

Question

One of my jobs is failing and it shows below message.

Error:

Could not allocate a new page for database 'Reporting' because 
of insufficient disk space in Filegroup ‘PRIMARY’.  
Create the necessary space by dropping objects in the filegroup, 
or setting autogrowth on for existing files in the filegroup. 
[SQLSTATE 42000] (Error 1101) The step failed.

Any idea how can this be resolved.

Thanks, Ar

Answer 1

In simple terms you've run out of space in the primary filegroup of the Reporting database.

The data in a database is stored in FileGroups. The main filegroup (and the one created by default) is Primary. By default this is where everything is put unless you specify otherwise. Most databases are just going to have the Primary filegroup.

The error you are getting is caused by running out of space in that Primary filegroup (obviously). This can be caused by one of two possibilities.

1. Insufficient disk space. Your database has expanded to the limit of the drive it's on. You can easily check this by looking at your server and seeing if the drive the database is on is full. The solutions here are to either expand the drive, reduce the amount of data in the database, or add additional files to the Primary filegroup on a different drive (or additional filegroups and move data into them).

2. Your autogrowth settings have limited your growth. This could be one of a couple of things.

   • You could have autogrowth turned off entirely. Basically you have used up the available space in the database and it can't grow by itself. Typically if you are going to do this then you need to be monitoring the free space in your database and manually grow it when you start running low.
   • You have a size limit on the files in the filegroup. In this case you can either increase the size limit (and manually grow the DB) or add additional files as I mentioned above.

To do any of these tasks (except looking at the physical space of the drive) you can use the GUI. Go to the properties of the database in SSMS. (Right click on the database name and select properties at the bottom). Then Navigate to the Files tab (you can only control the size of the files within the filegroup)

From here you can see which files are in the Primary filegroup and control their size (Initial Size) & autogrowth settings (hit the ellipsis ...)

The autogrowth settings look like this:

You can see here you can control the max size of the file & if autogrowth is allowed at all.

Answer 2

You will need to expand the filegroup, either by adding space to one of its files by extending them or by adding a new file.

Right click the 'Reporting' database and select properties and under files extend a datafile by changing the initial size, check that you have enough space on the disk drive the file resides on and then add at least 10% of the size of the file. Then change the the autogrowth settings and set those to more than 1MB, find a limit that can happen fast on your storage, 256MB is usually safe 512MB or larger if your storage is fast and if you have instant file allocation active

https://www.youtube.com/watch?v=xEvQjzzM3MY