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:
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:
- doing a
Things I cannot try without downtime/slow-time (I think), and therefore have not yet:
- Simple recovery -> do shrink -> Full recovery -> take full-backup to allow tlog-backups to continue
- 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.
- 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 EMPTYFILEworked fine in another environment for the same database.
Config: SQL 2017 Standard, 16 cores (2x8), 124 GB RAM. Platform: Nutanix with VMWare.