How to remove files and filesgroup from dis that have data in it?

Question

I have created files and filegroup under partitioning process. These secondary files have data in them. I want to remove these files as I have deleted the table from the database but I am finding it difficult to remove them. how can I remove them?

Answer 1

You have to clean the files before removing them.

Using command:

USE [YourDB]
GO
DBCC SHRINKFILE (N'YourFileLogicalName' , EMPTYFILE)

Using SSMS:

Choose your file and turn option: Empty file by migrating the data to other files in the same filegroup.

Answer 2

You need to remove all allocations from that filegroup. That includes indexes, tables and partitions that resides on that filegroup. Then you can remove the files and finally the filegroup.

For just a file and to keep other files for the same filegroup (not what I think you're asking about), then you can shrink that file using the EMPTYFILE option and then remove that file.

Answer 3

These queries may be helpful to you if you are looking for details in the FG's

--Find partition schema and partition function details on a DB
SELECT t.name AS TableName, ps.name AS PartitionScheme,
ps.data_space_id, pf.name AS PartitionFunction, pf.function_id
FROM sys.TABLES t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id


--To determine what object is on which FG
SELECT o.[name] AS TableName, i.[name] AS IndexName, fg.[name] AS FileGroupName
FROM sys.indexes i
INNER JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = fg.data_space_id AND o.type = 'U'