Today, I encountered below error on one my SQL Server which I manage.
Could not allocate space for object '%.*ls'%.*ls in database '%.*ls' because the '%.*ls' 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.
It occurred during Index Reorg operation. Upon investigation, I found that my DB was split in 3 filegroups and had multiple files in them. All of them had at least one file with auto-growth enabled and disks hosting them had enough free space available. So then why my index maintenance failed? My research led me to this MS article https://msdn.microsoft.com/en-us/library/aa337441.aspx which says:
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.
Ok. So the solution is to rebuild the index so it moves to the new file. But what if I want to continue having my index in same file so that my reorg can continue? Is that possible? Also, is it possible to find out in which file my DB object resides? Most of the blogs have scripts to find objects residing in filegroup but I want to find files in that filegroup so I can manually grow them and see if that fixes it.