Sql-server – Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

data-pagesdatafiledbccsql serversql-server-2008-r2

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;

But 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 ;).

EDIT:

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 :).

Best Answer

Restarting the server should be enough - those worktables should clear out. But I'd probably start it up in single user mode (-m) to prevent other processes from creating worktables before you successfully remove those files. Then redefine the files required for tempdb; perhaps deleting unnecessary files, changing sizes, etc. You should also ensure you have an even number of files, that they're all set to the same size, and that they all have the same autogrowth settings (in MB, not %). And it might be a good time to consider TF 1117 and TF 1118 as well (starting point).

I'd be very wary about the suggestion to just delete the files from the file system before starting SQL Server again - it might not start at all.

(I'd also be curious about what the actual problem is, though. Having too many files doesn't hurt you, really.)

Related Question