--分区属性
SELECT * FROM sys.partitions AS p
JOIN sys.tables AS t
ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
AND t.name = 'custom_file_error_log';
--查看分区方案段
select $partition.[PGTABLE_FORANA_FUNC](day) as [PGTABLE_FORANA_FUNC],count(*) as recordCount
from [dbo].pgtable_forana_log_test
group by $partition.[PGTABLE_FORANA_FUNC](day)
--查看分区详情
select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('custom_file_error_log')
and i.index_id in (0, 1)
order by p.partition_number
--1.移除历史记录(一周前的) [每天23:30执行一次]
declare @day int
set @day=365 --DateName(day,getdate())%30+10 --((select datepart(weekday,getdate()))%7+1)
alter table UpdateInfo_Hist10
switch partition @day
to UpdateInfo_Histtemp
--删除分区数据
declare @day int
set @day=365 --DateName(day,getdate()-10) --DateName(day,getdate())%30+10 --((select datepart(weekday,getdate()))%7+1)
alter table custom_file_error_log -- 分区表
switch partition @day
to custom_file_error_log_TMP --临时表
SELECT * FROM sys.partitions AS p
JOIN sys.tables AS t
ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
AND t.name = 'custom_file_error_log';
--查看分区方案段
select $partition.[PGTABLE_FORANA_FUNC](day) as [PGTABLE_FORANA_FUNC],count(*) as recordCount
from [dbo].pgtable_forana_log_test
group by $partition.[PGTABLE_FORANA_FUNC](day)
--查看分区详情
select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('custom_file_error_log')
and i.index_id in (0, 1)
order by p.partition_number
--1.移除历史记录(一周前的) [每天23:30执行一次]
declare @day int
set @day=365 --DateName(day,getdate())%30+10 --((select datepart(weekday,getdate()))%7+1)
alter table UpdateInfo_Hist10
switch partition @day
to UpdateInfo_Histtemp
--删除分区数据
declare @day int
set @day=365 --DateName(day,getdate()-10) --DateName(day,getdate())%30+10 --((select datepart(weekday,getdate()))%7+1)
alter table custom_file_error_log -- 分区表
switch partition @day
to custom_file_error_log_TMP --临时表