Tempdb is configured with 8 files and we are reducing them to 4. I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the dbcc shrinkfile
with the emptyfile clause, then the alter db command with the remove file clause. When this was attempted, the error
DBCC SHRINKFILE: Page 9:364016 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 19 Cannot move all contents of file "tempdev8" to other places to complete the emptyfile operation. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
My question:
Should I attempt to remove the locked temp tables by identifying them, then, re-run the dbcc statements, or, simply run an alter statement, then restart the instance, or restart the instance, then rerun the alter? I realize the issue that the temp tables are being used by tempdb consistently which is causing the remove file not to work.
tempdb
? That is likely what created the work table. – user507 Jan 8 '16 at 15:45