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

一、目的

在前面的文章中我已经介绍了SQL Server 合并（删除）分区解惑 和SQL Server 2005 分区模板与实例 和SQL Server 动态生成分区脚本，这篇文章就是在上面3篇文章衍生出来的。

我们的服务器的数据已经有了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, 
[UpdateTime] [datetime] 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 ,null)
    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,
            [Source] [nvarchar](250) NULL,
            [Url] [nvarchar](420) NULL,
            [ImageMd5s] [nvarchar](max) NULL,
            [ImageNumber] [int] NULL,
            [ContentLength] [int] NULL,
            [PublishOn] [datetime] NULL,
            [AddOn] [datetime] NULL,
            [IsReply] [bit] NOT NULL CONSTRAINT [DF_Temp_Archive_IsReply]  DEFAULT ((0)),
            [NeedUpdateIndex] [bit] NULL,
            [NeedUpdateCategory] [bit] NULL,
            [PageNumber] [nchar](25) NULL,
            [FloorCount] [int] NULL,
            [MainUrl] [nvarchar](425) NULL,
            [Opinion] [int] NULL DEFAULT ((3)),
            [Category] [nvarchar](128) NULL,
            [Layout] [nvarchar](128) NULL,
            [IsFrontPage] [bit] NULL,
            [FtpReceiveTime] [datetime] NULL,
            [CollectTime] [datetime] 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]

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

        --修改分区方案
        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)

    END
END