DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1

Question

In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate controller. Yes, this is a virtual platform.

So let's say we have MyCoolDB and its primary MDF is at D:\Data\MyCoolDB.mdf, with logical name MyCoolDB_data. I add 3 new files: E:\Data2\MyCoolDB_2.ndf, F:\Data3\MyCoolDB_3.ndf, and G:\Data4\MyCoolDB_4.ndf. That works.

Now I want to re-distribute the data into these new files. So I use the DBCC SHRINKFILE command with the EMPTYFILE option. Namely, DBCC SHRINKFILE ('MyCoolDB_data', EMPTYFILE);.

It runs for about 10-30 seconds, then FAILS, throwing the error as follows: DBCC SHRINKFILE: System table SYSFILES1 Page 1:32074080 could not be moved to other files because it only can reside in the primary file of the database.

This operation worked fine in DEV/TEST environment, and I got no such errors. What could be going wrong / different in PROD? What are my next-steps or other options? Because now we have 3 new NDF files sitting there with hardly anything in them (while, yes, "new data" is being distributed into them, the primary file is still huge in comparison), and storage folks are going to be banging down my door about all that "wasted space".

Things I have tried:

1. doing a CHECKPOINT beforehand

Things I cannot try without downtime/slow-time (I think), and therefore have not yet:

2. Simple recovery -> do shrink -> Full recovery -> take full-backup to allow tlog-backups to continue
3. Creating a new FILEGROUP, and adding files to new FILEGROUP, and using index rebuilds to move data into said new FILEGROUP's files. PS: Now that the new NDF files are there and have 'something' in them, I'm betting it's gonna be nearly impossible to UNDO that action (i.e. to EMPTY them and remove them), so I'm not looking forward to that being a thing, if it is.
4. Creating a new DB with the desired file structure and actually migrating all the data over to it. This seems like the last-resort answer. As I said, the SHRINKFILE EMPTYFILE worked fine in another environment for the same database.

Config: SQL 2017 Standard, 16 cores (2x8), 124 GB RAM. Platform: Nutanix with VMWare.

Answer 1

I ran into this, I did not want to remove all the data just move some of the data to other files in the filegroup to balance out the size of the datafiles in the primary filegroup.

and the answer is first shrink the file doing a regular shrink to rearrange the data in the file so the system files are at the bottom where they belong, then run the shrink file.

You typically don't have to do the first step for very long as that is the first thing it will attempt to do.

DBCC SHRINKFILE (N'MYFILEName' , 0)
GO

let it run for a while, that will move the sysfiles to bottom physically and then try

DBCC SHRINKFILE (N'MYFILEName' , EMPTYFILE)
GO

This is typically going to block activity so you may need to do it after hours.