10

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

  • DBCC PAGE command is not correct it should be like dbcc page(2,41920,1). 1,2,3 depends on what you want in output. – Shanky Sep 18 '14 at 10:49
  • If above does not works you might have to do it in hardware by removing files and then restarting sql server so that it would just used files which were not deleted. Can you refer to this link daveturpin.com/2011/07/how-to-drop-a-tempdb-database-file – Shanky Sep 18 '14 at 10:51
  • 2
    @Shanky The command is correct. 0..3 can be passed as 4th parameter: dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]) About your solution: it would work, but I'd really like to do this without bringing the instance down. – Adam Luniewski Sep 18 '14 at 10:58
  • Ok..I was telling you more simple form of dbcc page. Please note is undocumented command. Did you checked link i posted – Shanky Sep 18 '14 at 11:01
  • 1
    I don't think it's feasible to solve this problem without restarting the instance. Obviously you've tried tracking down what this worktable is (which would help determine who owns it), and that has failed. Take the hit, or live with the extra files until you can restart. – Aaron Bertrand Sep 18 '14 at 13:36
5

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

  • @@Aaron ofcourse you need to be cautious about removing it must be empty, but that is documented here msdn.microsoft.com/en-gb/library/ms175574.aspx. Have tried it and SQl Server tempdb comes online. – Shanky Sep 18 '14 at 13:07
  • 5
    @Shanky I tried driving 138 miles an hour once, and I didn't get pulled over. Does that mean I should keep doing it, and that there's no chance I'll get pulled over tomorrow? Much safer to try the proper way first, before suggesting a riskier way. IMHO. Especially since he can't empty the file now - do you think it's possible there's anything else in it besides the worktable the error message is complaining about? The error doesn't produce an exhaustive list of all objects, it only outputs the first object. – Aaron Bertrand Sep 18 '14 at 13:13
  • There is something which is actually using tempdb so it is not allowing it to empty file its hard to guess. May he used sort operator is some query which still needs tempdb. DMV can help that in finding sys.dm_db_task_space_usage sys.dm_db_session_space_usage sys.dm_db_file_space_usage – Shanky Sep 18 '14 at 13:58
  • Restarting is option here but even if he does not empty the file and try dropping tempdb file it would say tempdb file cannot be dropped but at same time system catalog is updated and after restart the file would be gone. This is I guess default behavior with tempdb so I suggested and still think removing data file would work even if it is utilized. The same is there in link I posted in my second comment. – Shanky Sep 18 '14 at 13:58
  • 1
    @Shanky those DMVs could return thousands of rows for all kinds of things that don't have anything to do with the file in question - so how do you plan to narrow it down? Also since with your method you have to restart too, why not just try the simpler way first? I just don't agree with you that "the hard way" should be the first suggestion, sorry. – Aaron Bertrand Sep 18 '14 at 14:10
2

https://social.msdn.microsoft.com/Forums/en-US/2a00c314-f35e-4900-babb-f42dcde1944b/dbcc-shrinkfile-page-411283400-could-not-be-moved-because-it-is-a-work-table-page?forum=sqldatabaseengine

As proposed by Mike in the msdn forum , work tables are mostly associated with plan cache. Clearing them would remove the work table as well and then you may shrink Tempdb. This worked for me. And this saves you a server restart as well. There will be some overhead since SQL server will have to recreate execution plans again.

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.