3

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?

2
  • 1
    Please see this. Duplicate of stackoverflow.com/questions/2183742/… Commented Apr 13, 2017 at 14:54
  • 4
    I find this the most alarming part of the question: I wanted to create a second log file... Rarely is there a need to create a 2nd log file. Paul Randal does a much better job of explaining why that's a bad idea, here. Commented Apr 13, 2017 at 15:01

2 Answers 2

4

Use:

DBCC SHRINKFILE('logicalname', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logicalname;
4
  • To be clear, this will only work if the file you're trying to move is part of an existing filegroup that contains other data files (like part of the DEFAULT filegroup). If the file you're trying to move is the only member of its filegroup, you'll have to 1) ALTER each table or index to move them to a different filegroup, 2) drop the file, 3) drop the filegroup.
    – BradC
    Commented Apr 13, 2017 at 18:19
  • Thanks a lot! But it didn't work. When I run DBCC SHRINKFILE('logicalname', EMPTYFILE); I get error A severe error occurred on the current command. The results, if any, should be discarded.
    – Hikari
    Commented Apr 17, 2017 at 14:44
  • Just checking you did replace logicalname with the logical name of the file you are trying to remove? Commented Apr 17, 2017 at 21:27
  • Yes. Even the query I posted became empty, and still the ndf wasn't being deleted.
    – Hikari
    Commented Apr 19, 2017 at 17:42
-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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.