SQLServer 表分区 根据时间自动扩展分区
前提条件
- 已经创建好分区方案
- 已经创建好分区函数
创建文件组
'alter database ' + @Database + ' add filegroup ' + [@fileGroupName]
创建文件并且将文件加入文件组
'alter database ' + @Database + ' add file ' +
'( ' +
'name = ''' + @FileName + ''',' +
'filename = ''' + @FilePath + '\' + @FileName + '.mdf '',' +
'size = ' + @FileSize + ',' +
'filegrowth = ' + @FileGrowth + ',' +
'maxsize = ' + @FileMaxLimit + '' +
')' +
'to filegroup ' + @FileGroupName
修改分区方案
'alter partition scheme ' + @SchemeName + ' next used ' + @FileGroupName
修改分区函数
这里注意range函数内日志格式,如果日志格式不对则会上下界限问题
'alter partition function ' + @PartitionName + ' split range (' + CONVERT(varchar(30), getdate(), 120) +')'
创建存储过程
- 以下存储过程是将上面每个步骤的整合
- 增加分区是针对分区方案和分区函数的,单纯增加分区这块来看,对表没有直接操作
- 基于第二条,凡是用了以上方案和函数的表都会增加分区
- 以下代码可以将exec替换成print 看从之态输出测试后在正式运行
- 如果想拿来就用建议测试测试,想深入点去官网看把…
create procedure AutoExtendTrainMainDataPartition
as
begin
declare
@FilePath varchar(100),--文件路径
@FileName varchar(100),--文件名称
@FileSize varchar(100),--文件大小
@FileGrowth varchar(100),--文件增长
@FileMaxLimit varchar(100),--文件最大限制
@FileGroupName varchar(100),--文件组名称
@Database varchar(100),--操作数据库
@CurrentDateTimeByYearAndMonth varchar(100),--当前时间,年月
@SchemeName varchar(100), --分区方案名称
@PartitionName varchar(100), --分区名称
@Random smallint,
@sql varchar(400)
-- 赋值文件属性
set @FileSize = '3MB'
set @FileGrowth = '10%'
set @FileMaxLimit = 'unlimited'
set @CurrentDateTimeByYearAndMonth = left(CONVERT(varchar(30), getdate(), 112), 6)
set @FileName = 'TrainMainData_' + @CurrentDateTimeByYearAndMonth
set @Random = cast(ceiling(rand() * 20) as int) -- 获取随机数,利用随机数决定当前执行脚本存放那块硬盘
if @Random % 2 = 0
set @FilePath = 'W:\yibiao\TrainMainDataPartitions'
else
set @FilePath = 'Y:\yibiao\TrainMainDataPartitions'
-- 赋值数据库属性
set @Database = 'YiBiaoData'
-- 赋值文件组属性
set @FileGroupName = 'TrainMainData_' + @CurrentDateTimeByYearAndMonth
-- 赋值分区属性
set @SchemeName = 'AutoExtendScheme'
set @PartitionName = 'AutoExtendPartition()'
--创建文件组
set @sql = 'alter database ' + @Database + ' add filegroup ' + @fileGroupName + ''
exec(@sql)
--创建组文件,将文件绑定于文件组
--创建文件,指定文件组
set @sql = 'alter database ' + @Database + ' add file ' +
'( ' +
'name = ''' + @FileName + ''',' +
'filename = ''' + @FilePath + '\' + @FileName + '.mdf '',' +
'size = ' + @FileSize + ',' +
'filegrowth = ' + @FileGrowth + ',' +
'maxsize = ' + @FileMaxLimit + '' +
')' +
'to filegroup ' + @FileGroupName
exec(@sql)
-- 修改分区方案
set @sql = 'alter partition scheme ' + @SchemeName + ' next used ' + @FileGroupName + ''
exec(@sql)
-- 修改分区函数
set @sql = 'alter partition function ' + @PartitionName + ' split range (' + CONVERT(varchar(30), getdate(), 120) +
')'
exec(@sql)
end
go
利用各种定时任务执行以上存储过程
由于分区界限和分区名称等都是用时间为准,建议每月第一天最早时间执行