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.