程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 設計方案:SQL Server 2005 自動化刪除表分區

設計方案:SQL Server 2005 自動化刪除表分區

編輯:關於SqlServer

設計目的

在前面的文章中我已經介紹了解析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,

 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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved