DBCC SHRINKFILE: Page : could not be moved because it is a work table page Cannot move all contents of file "" to other places to complete the emptyfile operation.

It was all good day and all was fine. Then there was something we were discussing. On one of my Server we had four datafiles in TempDB. Due to some reasons we needed to remove two datafiles from my TempDB. All fine. You cannot do this just like that when it is running as there might be some data in that datafiles. So what we need to do is first empty the file by migrating the data in this file to other data files in the same filegroup. I ran the below query to Empty the file and then Remove the File.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev2' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

Thought it would empty the file and then remove the file as normal case. Nope Bang I was hit with Error

DBCC SHRINKFILE: Page 4:55 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 5
Cannot move all contents of file "tempdev" to other places to complete the emptyfile operation.

Then I tried shrinking manually dint work. After googling and searching, found that If there are some persisted tables in TempDB and reside on the file you are trying to remove, it will not be able to Shrink or Remove. Then going through some technet site and then one of my previous post to clear cache, I found out a way. Below are the step by step instruction to achieve the same.

1. Find out all the tables which are in tempDB. Found out a very useful query posted by sslane in one of the Microsoft Technet/MSDN forum site which is always the best for me Under Link. Below Query is copied as it with just a small modification. Thanks to sslane and Microsoft for the same.

Query is:

use tempdb
declare @id int
declare @dt smalldatetime
create table #spt_space_all
(
id int,
name varchar(500),
rows varchar(200) null,
reserved varchar(200) null,
data varchar(200) null,
index_size varchar(200)null,
unused varchar(200) null,
create_date smalldatetime null,
)
declare TMP_ITEMS CURSOR LOCAL FAST_FORWARD for
select id from sysobjects
where xtype='U'
open TMP_ITEMS
fetch next from TMP_ITEMS into @id
declare @pages int
WHILE @@FETCH_STATUS = 0
begin
create table #spt_space
(
id int,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null,
create_date smalldatetime null,
)
set nocount on
if @id is not null
set @dt = (select crdate from sysobjects where id=@id [2])
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
update #spt_space set create_date=@dt [3]
end
insert into #spt_space_all
select name = @id,object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB'),create_date
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
drop table #spt_space
FETCH NEXT FROM TMP_ITEMS
INTO @id
end
CLOSE TMP_ITEMS
DEALLOCATE TMP_ITEMS
select 'DROP TABLE '+[name]+'
GO' from #spt_space_all where [name] not like '%#spt_space_all%'
drop table #spt_space_all
GO

2. Copy the Output and Run against the TempDB, dont worry if you get some errors, Let it drop all the tables which is possible and for those which is not possible it will throw error.

3. Run the below query to Empty The file and then Remove the File.

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

Done It was all set and I was able to remove the file successfully.

Note: No gurantee on Code, Use at your own risk, I have shared what I have just learnt and worked for me.

Happy Working.