Empty data file on SQL Server 2005


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.