Delete .ndf file

Question

Hi I used partitioning on my database. I created filegroup and four files in this filegroup. In this filegroup i dont have data and i need delete .ndf files from this filegroup. Files has 1MB size. i used this command: ALTER DATABASE MyDatabase REMOVE FILE MyFile i got error : Error 5042: The file 'MyFile' cannot be removed because it is not empty. I tried SHRINKFILE('logical_file_name' EMPTYFILE) but i got error: There is insufficient space in the filegroup to complete the emptyfile operation. Do you have any idea how i solve this problem. Thanks

Manish Kumar · Answer

Is there any objects or indexes are created on your ndf file.If yes then it should be remove permanently otherwise it will not allow to shrink ndf file.

Pavol · Answer

Step 2 i dont understand because i used $PARTITION to delete records from partition and i used DBCC SHRINKFILE (YOUR_NDF_FILE EMPTYFILE); .... i got error: There is insufficient space in the filegroup to complete the emptyfile operation.

Manish Kumar · Answer

Ok do the following steps. Step 1 Run below script SELECT OBJECT_NAME(object_id) * FROM sys.data_spaces ds INNER JOIN sys.allocation_units au ON ds.data_space_id = au.data_space_id INNER JOIN sys.partitions p ON au.container_id = CASE WHEN au.type = 2 THEN p.partition_id ELSE p.hobt_id END WHERE ds.name = 'YOUR NDF file NAME' This will give the result of objects index etc are created on your file group. Step 2: Either you have to move that objects to another ndf file or drop that one (not feasible on production) Step 3: RUN below command to empty your ndf file. DBCC SHRINKFILE (YOUR_NDF_FILE EMPTYFILE); Step 4: After this try to remove file from database property. Thanks

Pavol · Answer

I tried delete from properties and i had same error

Manish Kumar · Answer

Try to remove from database properties.