12

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

0

4 Answers 4

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

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

2

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 had faced same issue to remove data file in tempdb the way it worked for me as below.

1) Take user databases to single user to make sure tempdb is not getting used

ALTER DATABASE dbname
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

2) restart sql server services

3) empty tempdb data file using

DBCC SHRINKFILE ('tempDev5', EMPTYFILE);

4) Check used extents on data file this should be 1 for remove file to work

DBCC Showfilestats
go

5) remove the data file

ALTER DATABASE tempdb REMOVE FILE tempdev5

6) bring user database to multi user

ALTER DATABASE OSCAR
SET MULTI_USER;
GO

7) You may face issue in bringing user db active. Use Sp_who to find out any active connection and kill it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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