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.
Best Answer
Per Mike Rose's answer on the Microsoft Social site, I believe this should work:
There will be some performance hit for this as SQL Server will recreate Query Plans, but it should allow you to shrink
TEMPDB.