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

导读：随着技术的发展进步，如今，SQL Server 2005数据库自动化已经渐渐走近人们的生活，SQL Server 2005 自动化删除表分区也已经实现啦，下文中将为大家详细介绍SQL Server 2005 自动化删除表分区设计方案，希望对大家能够有所帮助。

我们的服务器的数据已经有了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 *** @Change_value = Change_Value,@PARTITION_value= Part_Value

FROM [dbo].[PartitionManage] WHERE IsDone = 0

IF(@Change_value <= @Max_value)–判断是否需要整理分区

BEGIN

#p#

创建一个临时表

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

【编辑推荐】

1. 将Access升级为SQL Server 2005
2. SQL SERVER 2005中的同步复制技术
3. 怎样在SQL Server 2005中用证书加密数据
4. 快速解决SQL server 2005孤立用户问题