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?
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.
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'