SQL Server deletes filegroups and files

File and filegroup deletion, if because the previous partition scheme is unreasonable, need to cancel partition, or partition in another way, you need to involve the deletion of files and filegroups, if not mastered the correct steps, sometimes can not be deleted, will prompt you "file is not empty, cannot delete" or "filegroup is not empty, Cannot delete "and so on, if do not know the skill, will be very depressed!" I have experienced such a depressed! Baidu also did not find the correct answer.
1, the deletion of files: first to clear the file in the data, delete before the data must remember to back up, you can copy the data to another table, and then execute:

DBCC shrinkfile (FileName, emptyfile);

After the contents of the file are deleted, the delete file command is executed, DatabaseName represents the data name, and filename indicates the file name:

ALTERDATABASE[DataBaseName] REMOVE FILE FileName;

2. File group deletion:
When the file group file is deleted, as normal understanding, you should be able to delete the filegroup directly, it is not practical, you can not delete filegroups.
Because there are a few things that depend on filegroups, one is a partitioning scheme, and the other is a partitioned table that uses the partitioning scheme.
Therefore, to delete a partition scheme, you can delete a filegroup. But before you delete a partition scheme, change the partition table that depends on it so that it does not depend on it.
This is mainly to change the partition table partition column, so that it does not use partition scheme, if it does not change, in the table data has been backed up, you can directly delete the table to resolve.
Then delete the partition table scheme, and finally you can delete the filegroup directly.
Summarize the previous removal process:
1, modify the partition table so that it does not depend on the partition scheme.
2. Delete the partition scheme (dependent on the filegroup to be deleted).

DROPPARTITION SCHEME [Part_func_scheme_Name]

3. Delete file groups directly.

ALTERDATABASE[DataBaseName] REMOVE FILEGROUP [FGName]

DatabaseName represents the data name, and Fgname represents the filegroup name.

SQL Server deletes filegroups and files