I have too many secondary data files (.ndf) created for
tempdb. To remove the excess files, I need to empty the file (content will be moved to other files):
DBCC SHRINKFILE('tempdbfile8', EMPTYFILE);
and then delete the file:
ALTER DATABASE tempdb REMOVE FILE tempdbfile8;
EMPTYFILE command returns the error:
DBCC SHRINKFILE: Page 8:41920 could not be moved because it is a work table page. Msg 2555, Level 16, State 1, Line 2 Cannot move all contents of file "tempdbfile8" to other places to complete the emptyfile operation.
Not to worry, I just need to locate the object that's using this page to do something about it:
DBCC TRACEON (3604) DBCC PAGE(2,8,41920) --dbid=2, fileid=8, pageid=41920
The command returns a lot of information, object_id among them. But:
Metadata: ObjectId = 0
I have no idea what to do about it. What cat be preventing this page from being moved? How to locate that object, process, session or whatever it is? Any help will be appreciated, but please note that leaving everything as it is or removing other file instead is not a valid solution to this problem ;).
I'm removing the files, because we used to follow "best practice" of creating one file per processor core (same initial size, same growth rate). But as far as I know, until you run into contention problems, there's no point to create additional tempdb files on the same device. In our case it makes sense, because we have MPIO turned on, and storage device can handle 4 paths. But there was a mistake, and we ended up with total 5 files with 6-core cpu. It's more than MPIO paths, less than CPU cores, and it's not an even number. It may not cause any problems, but just doesn't seem right :).
I was finally able to empty and remove the file without restarting the server by setting one of the datatabases (that I suspected of causing the problem) to single user mode (rollback immediate). It worked, but I got lucky. What I really want, is to be always able to track the page down :).