MSSQL 将大表改成已分区表
--select * from sys.partition_functions --select * from sys.partition_range_values use [UpdateLog] go CREATE TABLE [dbo].[ActionLog]( [Guid] [nvarchar](100) NOT NULL, [ActCode] [nvarchar](50) NULL, [ActSubCode] [nvarchar](50) NULL, [ActTime] [datetime] NULL, [ActInfo] [nvarchar](250) NULL, [ActType] [int] NULL, [KeyNum] [nvarchar](100) NULL, [IPAddress] [nvarchar](100) NULL, [MacAddress] [nvarchar](100) NULL, [OSVersion] [nvarchar](50) NULL, [OSBit] [nvarchar](50) NULL, [MemSize] [float] NULL, [CPUInfo] [nvarchar](50) NULL, [SoftName] [nvarchar](50) NULL, [OEMVersion] [nvarchar](50) NULL, [SoftBit] [nvarchar](50) NULL, [SoftVersion] [nvarchar](50) NULL, [AuthorType] [nvarchar](50) NULL, [UploadTime] [datetime] NOT NULL, ) ON UpdateLog_partition_scheme(UploadTime) ALTER TABLE [ActionLog] ADD CONSTRAINT [PK_ActionLog] PRIMARY KEY NONCLUSTERED ( [Guid] ASC,[UploadTime] ASC ) go CREATE CLUSTERED INDEX ix_ActionLog_UploadTime ON [ActionLog]([UploadTime]) go CREATE TABLE [dbo].[UpdateLogs]( [AutoKey] [int] IDENTITY(1,1) NOT NULL, [ActName] [varchar](200) NULL, [HostName] [varchar](200) NULL, [IP] [varchar](20) NULL, [OpDate] [varchar](50) NULL, [OpTime] [datetime] NOT NULL, [RetCode] [int] NULL, [SN] [varchar](100) NULL, [SoftName] [varchar](200) NULL, [SoftVer] [varchar](50) NULL, [IPAddress] [nvarchar](500) NULL, [YearNum] [int] NULL, [MonthNum] [int] NULL, [DayNum] [int] NULL, )ON UpdateLog_partition_scheme(OpTime) go ALTER TABLE [UpdateLogs] ADD CONSTRAINT [PK_UpdateLogs] PRIMARY KEY NONCLUSTERED ( [AutoKey] ASC,[OpTime] ASC ) go CREATE CLUSTERED INDEX ix_UpdateLogs_OpTime ON [UpdateLogs]([OpTime]) go --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('ActionLog') --and i.index_id in (0, 1) --order by p.partition_number --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('UpdateLogs') --and i.index_id in (0, 1) --order by p.partition_number
--DROP PARTITION SCHEME UpdateLog_partition_scheme --DROP PARTITION function UpdateLog_partition_func IF OBJECT_ID(N'tempdb.dbo.#Tab_Partition_Fun') IS NOT NULL BEGIN DROP TABLE #Tab_Partition_Fun END create table #Tab_Partition_Fun( fun_val_date datetime ) INSERT INTO #Tab_Partition_Fun(fun_val_date) values('2000-01-01 00:00:00'), ('2016-01-01 00:00:00'), ('2017-01-01 00:00:00'), ('2017-02-01 00:00:00'), ('2017-03-01 00:00:00'), ('2017-04-01 00:00:00'), ('2017-05-01 00:00:00'), ('2017-06-01 00:00:00'), ('2017-07-01 00:00:00'), ('2017-08-01 00:00:00'), ('2017-09-01 00:00:00'), ('2017-10-01 00:00:00'), ('2017-11-01 00:00:00'), ('2017-12-01 00:00:00'), ('2018-01-01 00:00:00'), ('2018-02-01 00:00:00'), ('2018-03-01 00:00:00'), ('2018-04-01 00:00:00'), ('2018-05-01 00:00:00'), ('2018-06-01 00:00:00'), ('2018-07-01 00:00:00') -- 文件组变量声明 declare @servername varchar(20) ; declare @filegroup varchar(64) ; -- 分组文件路径 declare @filegname varchar(300) ; declare @sql varchar(1024) ; declare @filenameprefix varchar(32); declare @filedir varchar(1024) ; declare @partition_func varchar(128) ; declare @partition_scheme varchar(128) ; declare @fun_val_date datetime declare @EveyMonth varchar(6) -- 变量赋值 set @servername = 'UpdateLog' ; -- 文件名前缀 set @filenameprefix = 'UpdateLogPart' ; -- 文件路径 set @filedir = 'D:\sqldata\'; -- 分区函数 set @partition_func = 'UpdateLog_partition_func'; -- 分区结构 set @partition_scheme = 'UpdateLog_partition_scheme'; set @sql = 'use '+ @servername; print @sql; exec(@sql); declare @filegroupArr varchar(8000) declare @fun_valArr varchar(8000) set @filegroupArr='' set @fun_valArr='' declare auth_cur cursor for select fun_val_date from #Tab_Partition_Fun open auth_cur fetch next from auth_cur into @fun_val_date while (@@fetch_status=0) begin set @EveyMonth=left(convert(varchar,dateadd(ss,-1,@fun_val_date),112),6); set @filegroup = @filenameprefix+'Group'+@EveyMonth; set @filegname=@filenameprefix+@EveyMonth+ '.ndf'; -- 添加文件组名 set @sql = 'alter database ' + @servername + ' add filegroup '+ @filegroup; print @sql; exec(@sql); -- 将文件组与文件名绑定 set @sql = 'alter database '+ @servername + ' add file (name=N'''+@filegroup+''',filename=N'''+@filedir+@filegname+''',size=5Mb,filegrowth=5mb) to filegroup '+@filegroup; print @sql; exec(@sql); set @filegroupArr=@filegroupArr+''''+CONVERT(varchar(100), dateadd(ss,-1,@fun_val_date), 21)+''',' set @fun_valArr=@fun_valArr+'['+@filegroup+'],' fetch next from auth_cur into @fun_val_date end close auth_cur deallocate auth_cur set @filegroupArr=left(@filegroupArr,len(@filegroupArr)-1) set @fun_valArr=left(@fun_valArr,len(@fun_valArr)-1) print @filegroupArr print @fun_valArr -- 创建分区函数 set @sql = 'create partition function '+ @partition_func +'(DATETIME) as range left for values('+@filegroupArr+')'; print @sql; exec(@sql); -- 创建分区结构(将分区函数和分区组对应起来) set @sql = 'create partition scheme '+ @partition_scheme +' as partition '+ @partition_func +' to('+@fun_valArr+',[Primary])'; print @sql; exec(@sql);
第三个建立自动建分区
USE [UpdateLog] GO /****** Object: StoredProcedure [dbo].[sp_CreatePartitionTabel] Script Date: 2018/7/25 13:43:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_CreatePartitionTabel] AS BEGIN declare @servername varchar(20) ; declare @sql varchar(1000); declare @filenameprefix varchar(32); declare @filedir varchar(1024) ; declare @partition_func varchar(128) ; declare @partition_scheme varchar(128) ; declare @cur_date varchar(128) ; declare @EveyMonth varchar(6); declare @filegroup varchar(200); declare @filegname varchar(200); -- 服务器名称 set @servername = 'UpdateLog' ; -- 文件名前缀 set @filenameprefix = 'UpdateLogPart' ; -- 文件路径 set @filedir = 'D:\sqldata\'; -- 分区函数 set @partition_func = 'UpdateLog_partition_func'; -- 分区结构 set @partition_scheme = 'UpdateLog_partition_scheme'; set @cur_date=dateadd(MM,1,GETDATE()); -- 文件组 set @EveyMonth = left(convert(varchar,dateadd(ss,-1,@cur_date),112),6); set @filegroup = @filenameprefix+'Group'+@EveyMonth; set @filegname=@filenameprefix+@EveyMonth+ '.ndf'; ---- 添加文件组 set @sql = 'alter database '+@servername+' add fileGroup '+@filegroup; print @sql; exec(@sql); -- 将文件组与文件名绑定 set @sql = 'alter database '+ @servername + ' add file (name=N'''+@filegroup+''',filename=N'''+@filedir+@filegname+''',size=5Mb,filegrowth=5mb) to filegroup '+@filegroup; print @sql; exec(@sql); -- 修改分区方案，用一个新的文件组用于存放下一新增的数据 set @sql='alter partition scheme '+ @partition_scheme +' next used '+@filegroup; print @sql; exec(@sql); -- 分区函数 set @sql= 'alter partition function ' + @partition_func +'() split range (''' + CONVERT(varchar(100), dateadd(ss,-1,@cur_date), 21)+''')'; print @sql; exec(@sql); END
利用SQL代理建立作业执行上面的存储过程
定时作业的制定 企业管理器 --管理 --SQL Server代理 --右键作业 --新建作业 --"常规"项中输入作业名称 --"步骤"项 --新建 --"步骤名"中输入步骤名 --"类型"中选择"Transact-SQL 脚本(TSQL)" --"数据库"选择执行命令的数据库 --"命令"中输入要执行的语句: EXEC 存储过程名 ... --该存储过程用于创建表 --确定 --"调度"项 --新建调度 --"名称"中输入调度名称 --"调度类型"中选择你的作业执行安排 --如果选择"反复出现" --点"更改"来设置你的时间安排 然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 设置方法: 我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
如果数据库代理意外关闭导至自己分区没有执行，当发现时可能晚了造成默认数据库文件特别大处理方法如下：
--1、补全之前的文件组 --2、新建数据库文件与对应有文件组对应起，注意文件路及文件名 --3、修改分区方案，注意：从前往后一个个设置 alter partition scheme UpdateLog_partition_scheme next used UpdateLogPartGroup201811 ; --4、修改分区函数 alter partition function [UpdateLog_partition_func]() split range ('2018-11-30T23:59:59.000')
