If you have the following error while empting a data file (with DBCC SHRINKFILE (N’datafilename’ , EMPTYFILE)):
Msg 2555, Level 16, State 2, Line 1
Cannot move all contents of file “xxxxxx” to other places to complete the emptyfile operation.
Check that the file do not contain tables with no clustered index. If a table has no clustered index cannot be moved. To check table contained in data file execute this script:
Create Table #Object_Search(
File_id Bigint,
Page_id Bigint,
pg_alloc Bigint,
ext_size Bigint,
object_id Bigint,
index_id Bigint,
partition_number Bigint,
partition_id Bigint,
iam_chain_type Varchar(50),
pfs_bytes varchar(50)
)
go
Insert into #Object_Search
EXEC('DBCC EXTENTINFO(''databasename'')')
GO
--File id for my data file, datafilename, is 7 therefore I just queried for that--
declare @id as int
select @id=file_id ('datafilename')
select distinct [name] from (
select object_name(object_id) as name,* from #Object_Search where File_id=@id
) x
With this list, add to every table in list a clustered index, repeat SHRINKFILE, an then drop index.