Sql-server – How to find the specific file in a filegroup in which an object resides

filegroupsindex-maintenancesql serversql-server-2012

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.

Best Answer

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?

Related Question