Could not allocate space for object in database because the Filegroup is full

Question

Today, I was hit by the below error on my SQL Server DB having multiple files in multiple filegroups.

Event ID: 1105. Could not allocate space for object in 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.

MSSQL 2014: Could not allocate space for object in database because the Filegroup is full

It occurred when my index maintenance job was running. Specifically during index reorg of one of the index. Upon investigation, I found that all my filegroups had at least one file with auto-growth enabled and all the auto-growth enabled files residing on respective drives had enough free space available. So there was no question of space issue or file autogrowth(considering I have at least one file in each filegroup with auto-growth enabled).Upon further drill down, found below from one of the MS link https://msdn.microsoft.com/en-us/library/aa337441.aspx

When an index is located on several files, ALTER INDEX REORGANIZE can return error 1105 when one of the files is full. The reorganization process is blocked when the process tries to move rows to the full file. To work around this limitation perform an ALTER INDEX REBUILD instead of ALTER INDEX REORGANIZE or increase the file growth limit of any files that are full.

So in order to fix my issue I went ahead and rebuild my index which fixed the issue. However, I am looking out for ways to avoid it as it may occur for different index in future and doing manual index rebuild all the time isn't a good way to fix it? I would love to hear your suggestions on this.

Answer 1

I'm looking for ways to avoid it in the future...

Don't have just a single file in the filegroup that has any free space. Set your files to the same size, grow them at the same time to the same sizes, and let the round robin with proportional fill algorithm do its' thing.

But, the auto-growth restricted files in filegroups are restricted due to space issues on the drives they reside. So enabling auto-growth for them or increasing their initial size to keep same across all files would be difficult.

Then they should be moved to different drives where there is space, or moved around so that all drives have space, or the drives expanded.

This really comes down to administration. This will be an ever present issue while the data files are vastly different in free space and size. The reason comes down to proportional fill and round robin allocations.

A very quick summary of proportional fill in SQL Server is that the file in the filegroup with the most free space should be allocated from more often than the ones with less free space. If there is a file with little to no free space, it'll be skipped. This means when you only have one file in your filegroup that really has any space, you're effectively hot-spotting all allocations there. The round robin part is just the allocations going between the files.

Since you're only keeping a single file with unbalanced amounts of free space, this will become a single hot spot for all allocations. This is why I advised the above in equaling your files as much as possible.