SQL Server 2005 自動(dòng)化刪除表分區(qū)設(shè)計(jì)方案
導(dǎo)讀：隨著技術(shù)的發(fā)展進(jìn)步，如今，SQL Server 2005數(shù)據(jù)庫(kù)自動(dòng)化已經(jīng)漸漸走近人們的生活，SQL Server 2005 自動(dòng)化刪除表分區(qū)也已經(jīng)實(shí)現(xiàn)啦，下文中將為大家詳細(xì)介紹SQL Server 2005 自動(dòng)化刪除表分區(qū)設(shè)計(jì)方案，希望對(duì)大家能夠有所幫助。
我們的服務(wù)器的數(shù)據(jù)已經(jīng)有了800G，并且每天進(jìn)數(shù)據(jù)大概有120W條記錄（數(shù)據(jù)空間大概為7G），而服務(wù)器現(xiàn)在已經(jīng)沒(méi)有太多的磁盤空間了，面對(duì)這樣的問(wèn)題，一般都是使用交換表分區(qū)來(lái)快速刪除數(shù)據(jù)，并使用之前的分區(qū)來(lái)存放新進(jìn)的數(shù)據(jù)，如果每次都人工的話就太麻煩了，所以我對(duì)這個(gè)如何進(jìn)行交換分區(qū)刪除數(shù)據(jù)來(lái)清理磁盤空間做成了自動(dòng)化。
分析與設(shè)計(jì)思路
分區(qū)特點(diǎn)：分區(qū)使用了自增ID作為分區(qū)字段；分區(qū)的索引進(jìn)行存儲(chǔ)位置對(duì)齊；
設(shè)計(jì)步驟1：表分區(qū)已經(jīng)確定了各個(gè)分區(qū)值，我們就用一個(gè)表保存可能存在的分區(qū)值，并插入到表中，當(dāng)達(dá)到預(yù)警值（Change_Value）時(shí)，我們就執(zhí)行交換分區(qū)；
設(shè)計(jì)步驟2：使用一個(gè)存儲(chǔ)過(guò)程來(lái)完成交換分區(qū)；
1) 創(chuàng)建一個(gè)臨時(shí)表
2) 交換分區(qū)數(shù)據(jù)
3) 刪除臨時(shí)表
4) 修改分區(qū)方案
5) 修改分區(qū)函數(shù)
設(shè)計(jì)步驟3：使用作業(yè)定時(shí)執(zhí)行存儲(chǔ)過(guò)程，實(shí)現(xiàn)自動(dòng)化；
參考腳本
下面是創(chuàng)建表的腳本和執(zhí)行交換分區(qū)的存儲(chǔ)過(guò)程，希望對(duì)你有幫助。
創(chuàng)建表
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]
插入數(shù)據(jù)
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: <分區(qū)管理>
-- =============================================
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)--判斷是否需要整理分區(qū)
BEGIN
#p#
創(chuàng)建一個(gè)臨時(shí)表
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)
--交換分區(qū)數(shù)據(jù)
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
刪除臨時(shí)表
DROP TABLE [dbo].[Temp_Archive]
修改分區(qū)方案
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)
修改分區(qū)函數(shù)
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
【編輯推薦】
