Unable to delete secondary data file

Question

I wanted to create a second log file, but by mistake I created a data file.

Now I'm trying to remove it, but I get the error message "the file cannot be removed because it is not empty". The following query lists a lot of tables:

SELECT o.name AS TableOrIndex
FROM sysfiles f
JOIN dbo.sysfilegroups s
ON f.groupid = s.groupid
JOIN dbo.sysindexes i
ON i.groupid = s.groupid
JOIN dbo.sysobjects o
ON i.id = object_id(o.name)
AND i.indid in (0, 1)
WHERE f.name = 'DB_NDF_FILE'

UNION

SELECT i.name
FROM sysindexes i join sysfilegroups f ON i.groupid = f.groupid
JOIN sysfiles l ON f.groupid = l.groupid
WHERE l.name = 'DB_NDF_FILE'

What's the correct procedure to move these data to other data files so I can safely delete this newly created file?

Answer 1

Use:

DBCC SHRINKFILE('logicalname', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logicalname;

Answer 2

I was unable to remove the ndf file. Even the query I posted on the question was being empty, meaning there was no table data on the ndf file, but still the remove command was failing.

I just copied create table code of all tables on the DB, removed the DB and recreated it with clean files. This is a ETL stage DB, there's no important data on it, so I could just "drop" all tables and recreate them.