I have a huge table with around 110 partitions. I wish to archive the oldest partition and drop the FileGroup. Following is the strategy I adopted.
我有一个大约110个分区的大桌子。我希望存档最旧的分区并删除FileGroup。以下是我采用的策略。
-
Created an exact empty table tablename_archive and met all partitioning requirements.
创建了一个精确的空表tablename_archive并满足所有分区要求。
-
Perform Partition switch
执行分区切换
ALTER TABLE tablename SWITCH PARTITION 1 TO tablename_archive PARTITION 1 -
After verifying the switch (partition swap) , I dropped the archived table.
验证交换机（分区交换）后，我删除了已归档的表。
-
Merged the Partition function using the first boundary value as follows
使用第一个边界值合并分区函数，如下所示
ALTER PARTITION FUNCTION YMDatePF2 () MERGE RANGE ('2012-01-01 00:00:00.000')
Although there is no data now on FG, when I try to drop the File or FG it errors out saying.
虽然现在FG上没有数据，但是当我尝试删除文件或FG时，它会出错。
The file 'XXXXXXXX' cannot be removed because it is not empty.
The filegroup 'XXXXXXXX' cannot be removed because it is not empty.
Is there any change I need to make it to Partition scheme too, after merging the function.
在合并函数之后，我是否需要对分区方案进行任何更改。
Please let me know if you need any more details.
如果您需要更多详细信息，请与我们联系。
1 个解决方案
#1
0
You can never remove the first (or only) partition from a RANGE RIGHT partition function (or conversely, the last (or only) partition of a RANGE LEFT function). The first (or last if RANGE LEFT) filegroup from the underlying partition schemes can never be removed from the schemes either. Remember you have one more partition, and partition scheme filegroup mapping, than partition boundaries.
永远不能从RANGE RIGHT分区函数中删除第一个（或唯一的）分区（或者相反，RANGE LEFT函数的最后一个（或唯一）分区）。无法从方案中删除基础分区方案中的第一个（或最后一个RANGE LEFT）文件组。请记住，除分区边界外，还有一个分区和分区方案文件组映射。
If your intent was to archive January 2012 data, you should have switched partition 2 rather than 1 because the first partition contained data less than '2012-01-01 00:00:00.000'. Now that the second partition has been merged, the first partition (and the first filegroup) contains data less than '2012-02-01T00:00:00.000', which includes January 2012 data.
如果您的目的是存档2012年1月的数据，则应该切换分区2而不是1，因为第一个分区包含的数据小于“2012-01-01 00：00：00.000”。现在已合并第二个分区，第一个分区（和第一个文件组）包含的数据小于“2012-02-01T00：00：00.000”，其中包括2012年1月的数据。
With a RANGE RIGHT sliding window, it is best to plan to keep the first filegroup empty. You could used the PRIMARY filegroup or a dummy one with no files for that purpose. See Table Partitioning Best Practices.
使用RANGE RIGHT滑动窗口，最好计划将第一个文件组保留为空。您可以使用PRIMARY文件组或虚拟文件组，没有文件用于此目的。请参阅表分区最佳实践。
#1
0
You can never remove the first (or only) partition from a RANGE RIGHT partition function (or conversely, the last (or only) partition of a RANGE LEFT function). The first (or last if RANGE LEFT) filegroup from the underlying partition schemes can never be removed from the schemes either. Remember you have one more partition, and partition scheme filegroup mapping, than partition boundaries.
永远不能从RANGE RIGHT分区函数中删除第一个（或唯一的）分区（或者相反，RANGE LEFT函数的最后一个（或唯一）分区）。无法从方案中删除基础分区方案中的第一个（或最后一个RANGE LEFT）文件组。请记住，除分区边界外，还有一个分区和分区方案文件组映射。
If your intent was to archive January 2012 data, you should have switched partition 2 rather than 1 because the first partition contained data less than '2012-01-01 00:00:00.000'. Now that the second partition has been merged, the first partition (and the first filegroup) contains data less than '2012-02-01T00:00:00.000', which includes January 2012 data.
如果您的目的是存档2012年1月的数据，则应该切换分区2而不是1，因为第一个分区包含的数据小于“2012-01-01 00：00：00.000”。现在已合并第二个分区，第一个分区（和第一个文件组）包含的数据小于“2012-02-01T00：00：00.000”，其中包括2012年1月的数据。
With a RANGE RIGHT sliding window, it is best to plan to keep the first filegroup empty. You could used the PRIMARY filegroup or a dummy one with no files for that purpose. See Table Partitioning Best Practices.
使用RANGE RIGHT滑动窗口，最好计划将第一个文件组保留为空。您可以使用PRIMARY文件组或虚拟文件组，没有文件用于此目的。请参阅表分区最佳实践。