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.

6
  • Are there any active transactions running under tempdb? That is likely what created the work table.
    – user507
    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
  • 2
    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. 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.
    – user507
    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
2

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

DBCC 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.

2
  • 2
    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
  • 1
    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). Jan 13 '16 at 14:06
0

I first run the following to see if I can remove the file without having to restart the sql service.

USE [tempdb]
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO
DBCC SHRINKFILE (N'temp5' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp5]
GO

If the files are blocked, then I alter the size and file growth. Then wait for the next service window were the sql service can be restarted. Then the files are small, empty, and locked for growth. Then you can easily delete them.

--before the sql server restart
USE [master]
ALTER DATABASE TempDB MODIFY FILE
(NAME = temp5, SIZE = 1KB, FILEGROWTH = 0 )
GO

--then after the sql server restart
USE [TempDB]
DBCC SHRINKFILE (N'temp5' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp5]
GO
1
  • I tried everything I found everywhere, and I couldn't get the tempdb file I wanted to shrink below 1MB, and it would instantly have a few hundred KB of usage when the server started. [1]Use the ALTER - 1KB query above. [2]Stop SQL Agent (to keep jobs from using tempdb as soon as the server started) [3][Restart SQL Server [4]Used the FREE EVERY_DAMN_THING shrink method before removing it [5]Restart SQL Agent. Sep 23 '21 at 15:30

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.