Sample Header Ad - 728x90

DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1

2 votes
1 answer
1944 views
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:
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.
Asked by NateJ (824 rep)
Mar 12, 2020, 02:48 PM
Last activity: Jan 20, 2025, 08:02 AM