2

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.

|improve this question
  • Are there any active transactions running under tempdb? That is likely what created the work table. – Shawn Melton Jan 8 '16 at 15:45
  • I did not check to see if there were any active transactions running under tempdb. On my next attempt, I will check that. – rvsc48 Jan 8 '16 at 15:49
  • 1
    If you're in a maintenance window, why not just restart the instance in single user mode to prevent anything (including SQL Server Agent) from doing anything in tempdb? Then make your changes, and restart SQL Server again normally. It seems like you are trying to avoid a restart in there but I don't see the point. – Aaron Bertrand♦ Jan 8 '16 at 15:50
  • On average anytime I have tried to remove tempdb files from a system I have had to restart it, especially active systems. – Shawn Melton Jan 8 '16 at 15:53
  • Do you remember if you had to restart it before you removed the files, or after, or both? – rvsc48 Jan 8 '16 at 15:56
1

Per Mike Rose's answer on the Microsoft Social site, I believe this should work:

FREESYSTEMCACHE ('ALL');

DBCC FREEPROCCACHE;

There will be some performance hit for this as SQL Server will recreate Query Plans, but it should allow you to shrink TEMPDB.

|improve this answer
  • Thanks Mike, yeah that's exactly what I ran: USE [tempdb] GO DBCC DROPCLEANBUFFERS GO DBCC FREEPROCCACHE GO DBCC FREESESSIONCACHE GO DBCC FREESYSTEMCACHE ( 'ALL') GO DBCC SHRINKFILE (N'tempdev8' , EMPTYFILE) If @@Error = 0 Begin ALTER DATABASE [tempdb] REMOVE FILE [tempdev8] END – rvsc48 Jan 8 '16 at 17:07
  • If this actually works, and this blog post suggests it might, though I was as surprised as the author, the performance hit should be irrelevant because you're going to want to restart SQL Server after reconfiguring tempdb anyway (at least I would be planning for that). – Aaron Bertrand♦ Jan 13 '16 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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