一、新建表同时分区
1、新建表同时分区：表结构：
2、选择数据库-属性
3、新建文件组：将要表的分区放到单独的文件组中，非必需
4、新建分区文件：尽量一个文件组对应一个分区文件，以后好维护，非必需
5、准备对表分区：右键点击要分区的表，选择存储—创建分区
6、点击下一步：选择分区列，以id为例
7、点击下一步，新建分区函数，随便命名，这里先命名为 testPartitionFun
8、点击下一步，新建分区方案，随便命名，这里先命名为 testPartitionSchame，点击下一步，进入映射分区界面，选择范围和该范围的文件组：
9、点击下一步、开始创建脚本：
10、点击下一步或者完成，进入分区进度界面在该界面查看分区结果：
11、在存储目录可以看到刚才建立的分区函数和分区方案：
12、使用sql语句查询，可以查到分区表的实际分区情况：
SELECT *FROM sys.partitionsAS p
JOIN sys.tablesAS t
ON p.object_id= t.object_id
WHERE p.partition_idIS NOT NULL
AND t.name= 'testPartition';
13、查看分区后的数据，需要通过表名和分区函数以及分区id：
select * from testPartition where$PARTITION.testPartitionFun(id)=1
select * from testPartition where$PARTITION.testPartitionFun(id)=2
select * from testPartition where$PARTITION.testPartitionFun(id)=3
14、查看每个分区的数据总数，通过使用表名和分区函数：
select $PARTITION.testPartitionFun(id)as 分区编号,count(id)as 记录数from testPartition groupby $PARTITION.testPartitionFun(id)
二、已分区的表增加分区
先修改增加分区方案的文件组和文件，再修改分区函数，添加分区边界
--分区架构中增加分区
ALTER PARTITION SCHEMEEMS_DATAACQUISITIONSCHEME NEXT USED [PRIMARY]
--分区方案中使用确定新分区的分区界限值
ALTER PARTITIONFUNCTION EMS_DATAACQUISITIONFUN() SPLITRANGE ('2016-09')
删除分区：
删除（合并）一个分区，事实上就是在分区函数中将多余的分界值删除，因此只需要修改分区函数，将本分区的数据向上合并到下一个分区，此时只是删除分区，数据并未删除
ALTER PARTITIONFUNCTION EMS_DATAACQUISITIONFUN() MERGE RANGE ('2016-01')
删除数据合并分区：
sqlServer分区表不能直接利用删除分区的方法删除数据，只能通过变通的方式删除
需要如下操作：
1.建一个和A表一样的B表，
2.然后用switch语句将A表中某天数据移动到B表，
3.再将B表整个drop，
4.最后用Merge命令将A表中的空白分区合并
--调整分区到空表
alter tableEMS_DATAACQUISITION switchpartition 1 to EMS_DATAACQUISITIONSWITCH
--将该分区转移到备份表
nsert intoEMS_DATAACQUISITIONBACKUPselect * from EMS_DATAACQUISITION
--删除该分区数据
truncate tableEMS_DATAACQUISITIONSWITCH
--删除分区
ALTER PARTITIONFUNCTION EMS_DATAACQUISITIONFUN() MERGE RANGE ('2016-01')
--完成
建立处理分区建立和删除的存储过程：利用EMS_DATAACQUISITIONSWITCH表做Switch滑动分区，然后将这部分数据转移到DATAACQUISITIONBACKUP表中，再清空DATAACQUISITIONSWITCH表，并合并分区
--=============================================
--Author: 尹
--Create date: 2016-06-06
--Description: 定时创建和删除分区
--=============================================
CREATE PROCEDURE [dbo].[ProcessPartition]
AS
BEGIN
declare@nextDate varchar(7),@sixMonthBeforDatevarchar(7),@twoYearBeforeDatevarchar(7),@nextCountint
select@nextDate = convert(varchar(7),dateadd(month,1,getdate()),120)
select@sixMonthBeforDate = convert(varchar(7),dateadd(month,-6,getdate()),120)
select@twoYearBeforeDate = convert(varchar(7),dateadd(month,-24,getdate()),120)
--检查是否需要增加分区
select@nextCount =(select count(1) from sys.indexes i
joinsys.partition_schemes psoni.data_space_id=ps.data_space_id
joinsys.destination_data_spaces dds
onps.data_space_id=dds.partition_scheme_id
joinsys.data_spaces ds2ondds.data_space_id=ds2.data_space_id
joinsys.partitions pon dds.destination_id= p.partition_number
andp.object_id= i.object_idand p.index_id = i.index_id
joinsys.partition_functions pfonps.function_id =pf.function_id
LEFTJOINsys.Partition_Range_valuesvon pf.function_id= v.function_id
andv.boundary_id=p.partition_number-pf.boundary_value_on_right
WHEREp.object_id= object_id('EMS_DATAACQUISITION')
andi.index_idin(0, 1)and v.value= @nextDate)
if(@nextCount=0)
begin
--分区架构中增加分区
ALTERPARTITION SCHEME EMS_DATAACQUISITIONSCHEME NEXT USED[PRIMARY]
--分区方案中使用确定新分区的分区界限值
ALTERPARTITIONFUNCTIONEMS_DATAACQUISITIONFUN() SPLITRANGE (@nextDate)
end
--检查是否需要删除分区
declarecheckPartition cursor for(
selectp.partition_number,convert(varchar(7),isnull(v.value,''), 20)as range_boundary
fromsys.indexes i
joinsys.partition_schemes psoni.data_space_id=ps.data_space_id
joinsys.destination_data_spaces dds
onps.data_space_id=dds.partition_scheme_id
joinsys.data_spaces ds2ondds.data_space_id=ds2.data_space_id
joinsys.partitions pon dds.destination_id= p.partition_number
andp.object_id= i.object_idand p.index_id = i.index_id
joinsys.partition_functions pfonps.function_id =pf.function_id
LEFTJOINsys.Partition_Range_valuesvon pf.function_id= v.function_id
andv.boundary_id=p.partition_number-pf.boundary_value_on_right
WHEREp.object_id= object_id('EMS_DATAACQUISITION')
andi.index_idin(0, 1)
)
opencheckPartition
declare@PartitionNumber int,@RangeBoundaryvarchar(7) --声明变量，用于读取游标中的值
fetch next fromcheckPartition into @PartitionNumber,@RangeBoundary
while @@fetch_status=0 --循环读取
begin
IF (@PartitionNumber>0and @RangeBoundary!=''and @RangeBoundary<=@sixMonthBeforDate)
begin
--调整分区到空表
altertable EMS_DATAACQUISITION switchpartition @PartitionNumber toEMS_DATAACQUISITIONSWITCH
--将该分区转移到备份表
insertinto EMS_DATAACQUISITIONBACKUPselect * from EMS_DATAACQUISITION
--删除该分区数据
truncatetable EMS_DATAACQUISITIONSWITCH
--删除分区
ALTERPARTITIONFUNCTIONEMS_DATAACQUISITIONFUN() MERGE RANGE (@RangeBoundary)
end
fetchnextfromcheckPartition into @PartitionNumber,@RangeBoundary
end
closecheckPartition --关闭
deallocatecheckPartition --删除
END
新建作业job，自动执行脚本为：
EXECProcessPartition
查询分区基本概况：
select ps.nameas partition_scheme,
p.partition_number,
ds2.name as filegroup,
isnull(v.value,'') as range_boundary,
p.rowsas rows
from sys.indexes i
join sys.partition_schemes pson i.data_space_id= ps.data_space_id
join sys.destination_data_spacesdds
on ps.data_space_id= dds.partition_scheme_id
join sys.data_spaces ds2on dds.data_space_id= ds2.data_space_id
join sys.partitions pon dds.destination_id= p.partition_number
and p.object_id= i.object_idand p.index_id = i.index_id
join sys.partition_functionspfon ps.function_id= pf.function_id
LEFT JOINsys.Partition_Range_values vonpf.function_id =v.function_id
and v.boundary_id= p.partition_number- pf.boundary_value_on_right
WHERE p.object_id= object_id('EMS_DATAACQUISITION')
and i.index_idin(0, 1)
order by p.partition_number
select *from EMS_DATAACQUISITION where$PARTITION.EMS_DATAACQUISITIONFUN(ACQUISITIONTIME)=1
select * from EMS_DATAACQUISITION where$PARTITION.EMS_DATAACQUISITIONFUN(ACQUISITIONTIME)=2
select * from EMS_DATAACQUISITION where$PARTITION.EMS_DATAACQUISITIONFUN(ACQUISITIONTIME)=9
插入语句：
declare @i datetime
set @i=CONVERT(DATETIME,'2015-01-01 00:00:00',20)
while @i<CONVERT(DATETIME,'2016-01-01 00:00:00',20)
begin
insert into EMS_DATAACQUISITION values(null,'a_102',convert(nvarchar(19),@i,20),'100','200','300');
set @i=dateadd(day,1,@i)
end
查询已经启用的job的执行状态
select b.name, a.step_name, msdb.dbo.agent_datetime( run_date, run_time) AS 'RunDateTime' ,
a.run_duration,
case when a.run_status=0 then 'Failed'
when a.run_status= 1 then 'Succeeded'
when a.run_status= 2 then 'Retry'
when a.run_status= 3 then 'Canceled'
else 'Unknown'
end as run_status,
a.[message]
from msdb .dbo. sysjobhistory a inner join msdb .dbo. sysjobs b on a.job_id =b .job_id
inner join msdb. dbo.sysjobsteps s on a .job_id = s .job_id and a.step_id = s .step_id
where b .enabled = 1
代理无法启用：
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE; --加上WITH OVERRIDE
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE WITH OVERRIDE --加上WITH OVERRIDE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE; --加上WITH OVERRIDE
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE WITH OVERRIDE --加上WITH OVERRIDE
GO
扫一扫
1156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
