2

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?

  • 1
    Please see this. Duplicate of stackoverflow.com/questions/2183742/… – SqlWorldWide Apr 13 '17 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. – John Eisbrener Apr 13 '17 at 15:01
4

Use:

DBCC SHRINKFILE('logicalname', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logicalname;
  • 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 Apr 13 '17 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 Apr 17 '17 at 14:44
  • Just checking you did replace logicalname with the logical name of the file you are trying to remove? – Andrew Lackenby Apr 17 '17 at 21:27
  • Yes. Even the query I posted became empty, and still the ndf wasn't being deleted. – Hikari Apr 19 '17 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, privacy policy and cookie policy

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