sqlServer2014分区过程及建立job自动添加删除分区脚本
一、新建表同时分区1、新建表同时分区：表结构：
2、选择数据库-属性
3、新建文件组：将要表的分区放到单独的文件组中，非必需
4、新建分区文件：尽量一个文件组对应一个分区文件，以后好维护，非必需
5、准备对表分区：右键点击要分区的表，选择存储—创建分区
6、点击下一步：选择分区列，以id为例
7、点击下一步，新建分区函数，随便命名，这里先命名为 testPartitionFun
8、点击下一步，新建分区方案，随便命名，这里先命名为 testPartitionSchame，点击下一步，进入映射分区界面，选择范围和该范围的文件组：
9、点击下一步、开始创建脚本：
10、点击下一步或者完成，进入分区进度界面在该界面查看分区结果：
11、在存储目录可以看到刚才建立的分区函数和分区方案：
? 12、使用sql语句查询，可以查到分区表的实际分区情况： 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 = '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 group by $PARTITION.testPartitionFun(id)? ? 二、已分区的表增加分区先修改增加分区方案的文件组和文件，再修改分区函数，添加分区边界 --分区架构中增加分区 ALTER PARTITION SCHEMEEMS_DATAACQUISITIONSCHEME?? NEXT USED [PRIMARY]? --分区方案中使用确定新分区的分区界限值 ALTER PARTITION FUNCTION EMS_DATAACQUISITIONFUN()? SPLIT RANGE ('2016-09')? ? ? ? 删除分区：删除（合并）一个分区，事实上就是在分区函数中将多余的分界值删除，因此只需要修改分区函数，将本分区的数据向上合并到下一个分区，此时只是删除分区，数据并未删除 ALTER PARTITION FUNCTION EMS_DATAACQUISITIONFUN()? MERGE RANGE ('2016-01')? ? 删除数据合并分区：sqlServer分区表不能直接利用删除分区的方法删除数据，只能通过变通的方式删除 需要如下操作： ? --调整分区到空表 alter tableEMS_DATAACQUISITION switch partition 1 to EMS_DATAACQUISITIONSWITCH --将该分区转移到备份表 nsert intoEMS_DATAACQUISITIONBACKUP select * from? EMS_DATAACQUISITION --删除该分区数据 truncate tableEMS_DATAACQUISITIONSWITCH --删除分区 ALTER PARTITION FUNCTION 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),@sixMonthBeforDate varchar(7),@twoYearBeforeDatevarchar(7),@nextCount int ??????? select@nextDate = convert(varchar(7),dateadd(month,1,getdate()),120) ??????? select@sixMonthBeforDate = convert(varchar(7),-6,120) ??????? select@twoYearBeforeDate = convert(varchar(7),-24,120) ? ??????? --检查是否需要增加分区 ??????? select@nextCount =(select count(1) from sys.indexes i ??????????????? joinsys.partition_schemes ps oni.data_space_id =ps.data_space_id ??????????????? joinsys.destination_data_spaces dds ??????????????? onps.data_space_id =dds.partition_scheme_id ??????????????? joinsys.data_spaces ds2 ondds.data_space_id =ds2.data_space_id ??????????????? joinsys.partitions p on dds.destination_id = p.partition_number ??????????????? andp.object_id = i.object_id and p.index_id = i.index_id ??????????????? joinsys.partition_functions pf onps.function_id =pf.function_id ??????????????? LEFTJOIN sys.Partition_Range_valuesv on 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_id in(0, 1) and? v.value= @nextDate) ? ??????? if(@nextCount=0) ??????? ???begin ??????????????? --分区架构中增加分区 ??????????????? ALTERPARTITION SCHEME EMS_DATAACQUISITIONSCHEME?? NEXT USED[PRIMARY]? ??????????????? --分区方案中使用确定新分区的分区界限值 ??????????????? ALTERPARTITION FUNCTIONEMS_DATAACQUISITIONFUN()? SPLIT RANGE (@nextDate) ??????????? end ? ??????? --检查是否需要删除分区 ??????? declarecheckPartition cursor for( ??????????????? selectp.partition_number,convert(varchar(7), isnull(v.value, ''), 20) as range_boundary ??????????????? fromsys.indexes i ??????????????? joinsys.partition_schemes ps oni.data_space_id =ps.data_space_id ??????????????? joinsys.destination_data_spaces dds ??????????????? onps.data_space_id =dds.partition_scheme_id ??????????????? joinsys.data_spaces ds2 ondds.data_space_id =ds2.data_space_id ??????????????? joinsys.partitions p on dds.destination_id = p.partition_number ??????????????? andp.object_id = i.object_id and p.index_id = i.index_id ??????????????? joinsys.partition_functions pf onps.function_id =pf.function_id ??????????????? LEFTJOIN sys.Partition_Range_valuesv on 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_id in(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 switch partition @PartitionNumber toEMS_DATAACQUISITIONSWITCH ??? ???????????????? --将该分区转移到备份表 ??? ???????????????? insertinto EMS_DATAACQUISITIONBACKUP select * from?EMS_DATAACQUISITION ??? ???????????????? --删除该分区数据 ??? ???????????????? truncatetable EMS_DATAACQUISITIONSWITCH ??? ???????????????? --删除分区 ??? ???????????????? ALTERPARTITION FUNCTIONEMS_DATAACQUISITIONFUN()? MERGE RANGE (@RangeBoundary) ??????????????????? end ??????????????? fetchnext fromcheckPartition into @PartitionNumber,@RangeBoundary ??????????? end ??????? closecheckPartition??? --关闭??? ??????? deallocatecheckPartition??? --删除 ??? END ? 新建作业job，自动执行脚本为： EXECProcessPartition ? 查询分区基本概况： select ps.name as partition_scheme, p.partition_number, ds2.name as filegroup, isnull(v.value, '') as range_boundary, p.rows as 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 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_functionspf on ps.function_id= pf.function_id LEFT JOIN sys.Partition_Range_values v onpf.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_id in(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,@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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
