这篇文章延续了表分区设计的建议。
我尝试过截断旧分区并合并它。但我收到错误“为表‘dbo.STYTOTAL_RAW’指定的分区号 8 无效，分区号范围为 1 到 6。”看起来一旦合并，while 循环对于最大分区号就变得无效。
为了测试我使用了 3 天，但最初的要求是删除超过 90 天的分区。
按照@DanGuzman 的说法，也添加了 PF。完整脚本可用。在我的第一个帖子中。我也已将其链接到此帖子的顶部。
--Create function, copy from your script
CREATE PARTITION FUNCTION PF_myDateRange ( [datetime2](7))
AS RANGE RIGHT FOR VALUES
(
'2024-06-01 23:59:59.9999999',
'2024-07-01 23:59:59.9999999',
'2024-08-01 23:59:59.9999999',
'2024-09-01 23:59:59.9999999',
'2024-10-01 23:59:59.9999999',
'2024-10-21 23:59:59.9999999' -- take the max day and round with 12 AM ex: 2024-10-21 00:00:00.0000000
)
GO
use DB_Partition
--Invalid partition number 8 specified for table 'dbo.STYTOTAL_RAW', partition number can range from 1 to 6.
-- We can loop through the partition number directly from the table
--TRUNCATE TABLE [STYTOTAL_RAW] WITH (PARTITIONS (4));
declare @cmd_1 nvarchar(max)
declare @cmd_2 nvarchar(max)
DECLARE @partition_no bigint
DECLARE @PartitionFunction_name nvarchar(128)
DECLARE @PartitionFunction_Upper_value datetime2(7)
DECLARE @minrow int
DECLARE @maxrow int
select @minrow = MIN(p.partition_number), @maxrow = MAX(p.partition_number)
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
where i.object_id = object_id('STYTOTAL_RAW')
and rv.value < DATEADD(DAY, -3, SYSDATETIME())
select @minrow,@maxrow
while (@minrow <=@maxrow)
begin
select @partition_no=partition_number,@PartitionFunction_name=pf.name,@PartitionFunction_Upper_value=cast(rv.value as datetime2(7))
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
where i.object_id = object_id('STYTOTAL_RAW')
and rv.value < DATEADD(DAY, -3, SYSDATETIME())
and p.partition_number = @minrow
SET @cmd_1 = N'TRUNCATE TABLE dbo.STYTOTAL_RAW WITH (PARTITIONS (' + convert(NVARCHAR(128),@partition_no) + N'));'
print @cmd_1
--EXEC sys.sp_executesql @cmd_1
SET @cmd_2 = N'ALTER PARTITION FUNCTION ['+ @PartitionFunction_name+ '] () merge range ('''+convert (NVARCHAR(128), @PartitionFunction_Upper_value) +''');'
print @cmd_2
--EXEC sys.sp_executesql @cmd_2
set @minrow =@minrow +1
end
请提出任何建议。感谢您的帮助。
