SQL Server 2005 自动化删除表分区设计方案

日期： 2011-02-28 作者：gaizai 来源：TechTarget中国 英文

　　一、目的

　　我们的服务器的数据已经有了800G，并且每天进数据大概有120W条记录(数据空间大概为7G)，而服务器现在已经没有太多的磁盘空间了，面对这样的问题，一般都是使用交换表分区来快速删除数据，并使用之前的分区来存放新进的数据，如果每次都人工的话就太麻烦了，所以我对这个如何进行交换分区删除数据来清理磁盘空间做成了自动化。

　　二、分析与设计思路

　　分区特点：分区使用了自增ID作为分区字段;分区的索引进行存储位置对齐;

　　设计步骤1：表分区已经确定了各个分区值，我们就用一个表保存可能存在的分区值，并插入到表中，当达到预警值(Change_Value)时，我们就执行交换分区;

　　设计步骤2：使用一个存储过程来完成交换分区;

　　1) 创建一个临时表

　　2) 交换分区数据

　　3) 删除临时表

　　4) 修改分区方案

　　5) 修改分区函数

　　设计步骤3：使用作业定时执行存储过程，实现自动化;

　　三、参考脚本

　　下面是创建表的脚本和执行交换分区的存储过程，希望对你有帮助。

–创建表
CREATE TABLE [dbo].[PartitionManage](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Change_Value] [int] NULL,
    [Part_Value] [int] NULL,
    [IsDone] [bit] NULL,
 CONSTRAINT [PK_PartitionManage] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

–插入数据
declare @i int 
declare @maxValue int
set @i=10
set @maxValue=100
while @i <= @maxValue
begin 
    insert into dbo.PartitionManage values(@i-4,@i,0)
    set @i = @i + 10
end 

— =============================================
— Author:    <Viajar>
— Create date: <2011.02.22>
— Description:   <分区管理>
— =============================================
CREATE PROCEDURE [dbo].[sp_PartitionManage]
AS
BEGIN
    DECLARE @Max_value INT
    DECLARE @Change_value INT
    DECLARE @PARTITION_value INT
    SELECT @Max_value = MAX(Id) FROM [dbo].[Archive]
    SELECT TOP 1 @Change_value = Change_Value,@PARTITION_value= Part_Value
       FROM [dbo].[PartitionManage] WHERE IsDone = 0
    IF(@Change_value <= @Max_value)–判断是否需要整理分区
    BEGIN
       –创建一个临时表
       DECLARE @sql VARCHAR(MAX)
       SET @sql = ‘
       IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”[dbo].[Temp_Archive]”) AND type in (N”U”))
       BEGIN
           DROP TABLE [dbo].[Temp_Archive]
       END’
       EXEC (@sql)
       SET @sql = ‘
       IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”[dbo].[Temp_Archive]”) AND type in (N”U”))
       BEGIN
       CREATE TABLE [dbo].[Temp_Archive](
           [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
           [SiteId] [int] NULL,
           [Title] [nvarchar](4000) NULL,
           [Author] [nvarchar](4000) NULL,
           [Content] [nvarchar](max) NULL,
        CONSTRAINT [PK_Temp_Archive] PRIMARY KEY CLUSTERED 
       (
           [Id] ASC
       )WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_Archive_Id](Id)
       ) ON [Sch_Archive_Id]([Id])
       END’
       EXEC (@sql)

       –交换分区数据
       DECLARE @Min_Id INT
       DECLARE @PARTITION_num INT
       SELECT @Min_Id = MIN(Id) FROM [dbo].[Archive]
       SELECT @PARTITION_num = [Archives].$PARTITION.Fun_Archive_Id(@Min_Id);
       ALTER TABLE [dbo].[Archive] SWITCH PARTITION @PARTITION_num TO [dbo].[Temp_Archive] PARTITION @PARTITION_num

       –删除临时表
       DROP TABLE [dbo].[Temp_Archive]

       –修改分区方案
       DECLARE @PARTITION_string varchar(50)
       SET @PARTITION_string = ‘FG_Archive_Id_’ + RIGHT(‘0’ + CONVERT(NVARCHAR,@PARTITION_num),2)
       SET @sql = ‘ALTER PARTITION SCHEME [Sch_Archive_Id] NEXT USED [‘+@PARTITION_string+’]’
       EXEC (@sql)

       –修改分区函数
       SET @sql = ‘ALTER PARTITION FUNCTION Fun_Archive_Id() SPLIT RANGE (‘+CONVERT(VARCHAR(50),@PARTITION_value)+’)’
       EXEC (@sql)

       –更新表
       UPDATE [dbo].[PartitionManage] SET IsDone = 1 WHERE Change_Value = @Change_value
    END
END

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

作者

gaizai
gaizai

相关推荐