Mar 20, 2017 9:30:12 PM by Pinal Dave
This is a companion discussion topic for the original entry at https://blog.toadworld.com/2017/03/21/sql-server-dbcc-shrinkfile-page-126423878-could-not-be-moved-because-it-is-a-work-table-page-tempdb-files
This is a companion discussion topic for the original entry at https://blog.toadworld.com/2017/03/21/sql-server-dbcc-shrinkfile-page-126423878-could-not-be-moved-because-it-is-a-work-table-page-tempdb-files
I tried this and I was not able to reduce the file sizes for the NDF files. When connected using the /mSQLCMD parameter, I can only see the primary MDF and the log file. I was unable to see the additional NDF files that I needed to reduce the file sizes on. This is described in the MS article: https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server.
To resolve the matter, I had to restart the SQL server multiple times and run DBCC FREEPROCCACHE multiple times. It had to empty/delete each individual ndf file and sometimes restart SQL again.
Once I got rid of some of the extra NDFs, everything return to "normal". I was then able to reduce the final NDFs manually. I did a final restart to make sure everything all the data files return to starting sizes.