2

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:

  1. Simple recovery -> do shrink -> Full recovery -> take full-backup to allow tlog-backups to continue
  2. 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.
  3. 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.

2
  • Are you sure it worked in your other environment? You might be able to shrink the primary file down to 1mb but I don’t think you can mark it to be emptied. Doing so I would imagine would make the database unusable as I’m assuming there pointers and mappings that are needed which only live in the primary data file
    – Aaron
    Mar 13, 2020 at 8:07
  • @Aaron Yes, it "worked' as in it did what I expected it to do, which was shrink the primary MDF down to a smaller size. No, I was not expecting it to completely empty -- I know that's impossible. I was expecting it to shrink the MDF and proportionally fill the NDFs so that they were all relatively equal. This did not happen in PROD.
    – NateJ
    Mar 14, 2020 at 19:02

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.