About deleting files and filegroups from partitioned tables in SQL Server (GO)

When you partition the table in SQL Server, it must involve files and filegroups, about how files and filegroups are created on the Internet a lot of information, my blog also has two related reprint documents, you can see, I this is no longer detailed, here are mainly about a few general on the Internet rarely mentioned things, but sometimes it is very important.

The preceding content is primarily excerpted from the online Help for SQL:

1. A file or filegroup cannot be used by multiple databases. For example, no other database can use files sales.mdf and sales.ndfthat contain data and objects in the sales database.

2. A file can only be a member of a filegroup.

3, a filegroup can contain multiple files, a data table can be created to specify that you want to put the data on that filegroup, and there is no way to specify which file to put on, the filegroup to all files within the group using the proportional fill policy.

4. Transaction log files cannot belong to any filegroup.

Some general recommendations when working with files and filegroups:

• Most databases perform well in the case of only a single data file and a single transaction log file.

• If you are using more than one file, create a second filegroup for the attached file and set it as the default filegroup. This way, the primary file will contain only system tables and objects.

• To maximize performance, create a file or filegroup on as many different, available local physical disks as you want. Place the objects that compete for the most space in different filegroups.

• Use filegroups to place objects on a specific physical disk.

• Place different tables that are used in the same join query in different filegroups. Performance is improved because parallel disk I/O is used to search for joined data.

• Place the most frequently accessed tables and the nonclustered indexes that belong to these tables in different filegroups. If the file is on a different physical disk, performance will improve due to parallel I/O.

• Do not place the transaction log files on a physical disk where there are other files and filegroups.

Filegroups use a proportional fill policy resolution for all files within a group:

When data is written to a filegroup, the SQL Server database engine writes data to each file in the filegroup by the percentage of free space in the file, rather than writing all the data to the first file until it becomes full. And then write the next file. For example, if the file F1 has a free space of up to three megabytes, the file F2 has a free space of up to five megabytes, a zone is allocated from the file F1 , two extents are allocated from the file F2 , and so on. In this way, two files are almost filled in at the same time, and a simple stripe can be obtained.

Assuming that the database is set to autogrow, when all the files in the filegroup are filled up, the database engine automatically expands one file to accommodate more data at a time by looping. For example, a filegroup consists of three files that are set to autogrow. Only the first file is extended when all the files in the filegroup have been exhausted. When the first file is full and no more data can be written to the filegroup, the second file is expanded. When the second file is full and no more data can be written to the filegroup, the third file is expanded. When a third file is full and no more data can be written to the filegroup, the first file is extended again, and so on

The content of their own practice process groping:

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, the following talk about my own after groping to get the 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:

ALTER DATABASE [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).

DROP PARTITION SCHEME [Part_func_scheme_name]

3. Delete file groups directly.

ALTER DATABASE [DataBaseName] REMOVE FILEGROUP [Fgname]

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

About deleting files and filegroups from partitioned tables in SQL Server (GO)