Delete Filegroup and File from database

Question

This is my database structure:

• 4 filegroups: PRIMARY, FileGroup201708, FileGroup201709, FileGroup201710
• 4 files: File1.mdf, File201708.ndf, File201709.ndf, File201710.ndf

This is the partition scheme and Function:

CREATE PARTITION FUNCTION [MyFunction](datetime) 
AS RANGE RIGHT FOR VALUES (N'2017-08-01T00:00:00.000', N'2017-09-01T00:00:00.000', N'2017-10-01T00:00:00.000')

CREATE PARTITION SCHEME [MyScheme] 
AS PARTITION [MyFunction] TO ([PRIMARY], [FileGroup201708], [FileGroup201709], [FileGroup201710])

All table in the database are partitioned.

Well, I decided to delete all data for 201708 period, first I perform :

TRUNCATE TABLE MyTable WITH (PARTITIONS (partition_number)) 

for all table. After this operation there isn't any data for partition in table.

SELECT
    p.partition_number, fg.name, p.rows, OBJECT_NAME(p.object_id)
FROM
    sys.partitions p
INNER JOIN 
    sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN 
    sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE
    p.object_id = OBJECT_ID('MyTable')

The query above give me 0 rows for the specific filegroup.

Now I want to delete the file and the filegroup, when I try to delete the file there is no problem, but when I try to delete a filegroup, I get an error from SQL Server:

The filegroup 'FileGroup201708' cannot be removed because it is not empty

I checked if there are some indexes in that filegroup using this query:

SELECT 
    OBJECT_NAME(i.[object_id]) AS [ObjectName],
    i.[index_id] AS [IndexID],
    i.[name] AS [IndexName],
    i.[type_desc] AS [IndexType],
    i.[data_space_id] AS [DatabaseSpaceID],
    f.[name] AS [FileGroup],
    d.[physical_name] AS [DatabaseFileName]
FROM 
    [sys].[indexes] i
INNER JOIN 
    [sys].[filegroups] f ON f.[data_space_id] = i.[data_space_id]
INNER JOIN 
    [sys].[database_files] d ON f.[data_space_id] = d.[data_space_id]
INNER JOIN 
    [sys].[data_spaces] s ON f.[data_space_id] = s.[data_space_id]
WHERE 
    OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
ORDER BY 
    OBJECT_NAME(i.[object_id]), f.[name], i.[data_space_id]

But all rows have in the column "FileGroup", PRIMARY filegroup. Can anyone give some suggestion?

Answer 1

I had the same problem (not being able to delete filegroups). What I did was:

1. DROP TABLE MyTable

2. DROP Files inside those FileGroups

   ALTER DATABASE MyDB REMOVE FILE logical_file_name_found_in_db_properties_in_files_section

3. DROP PARTITION SCHEME MyScheme

4. DROP PARTITION FUNCTION MyFunction

5. DROP FileGroups

Answer 2

This was my solution:

ALTER TABLE Fisier DROP column Fisier
GO
ALTER TABLE Fisier SET (FILESTREAM_ON="NULL")
GO
ALTER TABLE FisierRev DROP column Fisier
GO
ALTER TABLE FisierRev SET (FILESTREAM_ON="NULL")
GO
ALTER TABLE Contract DROP column Fisier
GO
ALTER TABLE Contract SET (FILESTREAM_ON="NULL")
GO

USE gestiunea_inan_19Rebut
GO
DBCC SHRINKFILE (N'inan_magazin5_fs', EMPTYFILE)
GO


EXEC sp_filestream_force_garbage_collection @dbname = N'gestiunea_inan_19Rebut'

ALTER Database gestiunea_inan_19Rebut REMOVE FILE inan_magazin5_fs
GO
ALTER Database gestiunea_inan_19Rebut REMOVE FILEGROUP FILESTREAMGROUP
GO


IF COL_LENGTH('Fisier','Fisier') IS NULL
BEGIN
    ALTER TABLE Fisier ADD Fisier [varbinary](max)   NULL
END
GO
IF COL_LENGTH('FisierRev','Fisier') IS NULL
BEGIN
    ALTER TABLE FisierRev ADD Fisier [varbinary](max)   NULL
END
GO

IF COL_LENGTH('Contract','Fisier') IS NULL
BEGIN
    ALTER TABLE Contract ADD Fisier [varbinary](max)   NULL
END
GO

Answer 3

Try it

GO
ALTER PARTITION FUNCTION MyFunction() MERGE RANGE (31);
GO
ALTER DATABASE [MYDB] REMOVE FILE [File31]
GO
ALTER DATABASE [MYDB] REMOVE FILEGROUP [FileGroup_31]
GO

Answer 4

First DBCC Shrinkfile for truncate, Then remove the filegroup from the partition scheme then

ALTER DATABASE [db name] REMOVE FILE [file name] GO

ALTER DATABASE [db name] REMOVE FILEGROUP [FileGroup201708] GO