SQL Server – How to Turn Off Auto Growth for a File

sql server

We have a primary file group with several files each on different disks. One of the disks is nearing capacity and as such we have had to switch off the auto growth for that file. Is there anything I need to consider when I do this?

I don't believe I can force a table to be in a particular file when they are all in the same filegroup, so from an admin point of view I haven't been able to think of anything.

Best Answer

I don't believe I can force a table to be in a particular file when they are all in the same filegroup

That's correct, you do not have the direct granular control over file allocations, only filegroup.

With that in mind, you have a few options. The first one would be to create a new filegroup and isolate whatever tables you need to in order to give you your desired end result of not filling up the drive. Another one, which I'd argue is probably one of the better options, is to put the file in question in a location with more space, or tack on more space to the existing volume. I'm guessing you wouldn't be asking this question if that was a possibility, though.

Another route would be to take advantage of the proportional fill behavior that SQL Server implements (which dictates it'll attempt to fill up all filegroup files at the same time) and size out (larger) the other files in the filegroup. That should translate to a larger percent of the allocations in those other files, as they'll demand more by the ratio of free space (reference this BOL document to further understand proportional fill). Although I personally think that's a hack and a workaround at best, and will only prolong the inevitable of filling up your drive.

Related Solutions

SQL Server – Handling Data Files Outgrowing Hardware

1) Yes

2) Yes,

After creating the new files, you'll need to drop and re-create the clustered indexes of the target tables, moving them on to the new filegroup to move the data:

CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [filegroup_name]

3) If you can get the DBA to back you up on this solution, I think you'll be OK. Based on your limitations this seems like a perfectly reasonable move - but remember to backup before trying to move the data!

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

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?

Best Answer

Related Solutions

SQL Server – Handling Data Files Outgrowing Hardware

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

Related Question