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.
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.