How to find the specific file in a filegroup in which an object resides

Question

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.

Answer 1

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?

As it says in the product documentation

Reorganize and Rebuild Indexes: (emphasis added)

The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. So although the filegroup might have free pages available, the user can still encounter error 1105...

---

But what if I want to continue having my index in same file so that my reorg can continue? Is that possible?

Yes, you just need to ensure that the necessary space exists beforehand, or that the file that needs to grow can do so.

Also, is it possible to find out in which file my DB object resides?

This is not as straightforward as one might expect. To get down to the file level accurately, you need to use the undocumented sys.dm_db_database_page_allocations (SQL Server 2012+) or DBCC IND.

A correct query to do this can be found on this Database Administrators Q & A:

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

Answer 2

The rebuild isn't necessarily going to move your index to a new file, it essentially drops and recreates the index, and it will be spread across the files in the file group per the Proportional fill round robin algorithm If one of the files needs to grow, it can do that.

If you want to do the REORG, manually grow the file that is low on space and do the REORG.

To see what file(s) your table has data on you can use this query (Code obtained from https://stackoverflow.com/questions/20129001/see-what-data-is-in-what-sql-server-data-file):

select 
 OBJECT_NAME(p.object_id) as my_table_name, 
 u.type_desc,
 f.file_id,
 f.name,
 f.physical_name,
 f.size,
 f.max_size,
 f.growth,
 u.total_pages,
 u.used_pages,
 u.data_pages,
 p.partition_id,
 p.rows
from sys.allocation_units u 
 join sys.database_files f on u.data_space_id = f.data_space_id 
 join sys.partitions p on u.container_id = p.hobt_id
where 
 u.type in (1, 3) and 
 OBJECT_NAME(p.object_id) = '<your table name>'
GO