34,588
社区成员
发帖
与我相关
我的任务
分享
--创建分区函数,设置了四个分割线
CREATE PARTITION FUNCTION PF_RangeByDate(datetime)
AS
RANGE RIGHT FOR VALUES (
'20060101', '20070101','20080101','20090101')
--创建分区文件群组(文件组),ndf文件
ALTER DATABASE fenqu ADD FILEGROUP [FG_DownLog2005]
ALTER DATABASE fenqu ADD FILEGROUP [FG_DownLog2006]
ALTER DATABASE fenqu ADD FILEGROUP [FG_DownLog2007]
ALTER DATABASE fenqu ADD FILEGROUP [FG_DownLog2008]
ALTER DATABASE fenqu ADD FILEGROUP [FG_DownLog2009]
--创建分区物理文件,指定逻辑名和物理名(存放路径),最后加入到相应的文件群组中
ALTER DATABASE fenqu
ADD FILE (NAME = N'Downlog2005',FILENAME = N'F:\Downlog2005.ndf')
TO FILEGROUP [FG_DownLog2005]
ALTER DATABASE fenqu
ADD FILE (NAME = N'Downlog2006',FILENAME = N'F:\Downlog2006.ndf')
TO FILEGROUP [FG_DownLog2006]
ALTER DATABASE fenqu
ADD FILE (NAME = N'Downlog2007',FILENAME = N'F:\Downlog2007.ndf')
TO FILEGROUP [FG_DownLog2007]
ALTER DATABASE fenqu
ADD FILE (NAME = N'Downlog2008',FILENAME = N'F:\Downlog2008.ndf')
TO FILEGROUP [FG_DownLog2008]
ALTER DATABASE fenqu
ADD FILE (NAME = N'Downlog2009',FILENAME = N'F:\Downlog2009.ndf')
TO FILEGROUP [FG_DownLog2009]
--创建分区方案
CREATE PARTITION SCHEME PS_ByDate
AS PARTITION PF_RangeByDate TO ([FG_DownLog2005],[FG_DownLog2006],[FG_DownLog2007],[FG_DownLog2008],[FG_DownLog2009])
--合并相邻的两个分区,参数是该两个分区之间的分割线
alter partition function PF_RangeByDate() MERGE RANGE('20060101')
--删除合并完后剩余的那个没用的文件,先清空,然后清除
dbcc shrinkfile('Downlog2006',EMPTYFILE)
--删除文件,然后删除文件组
ALTER DATABASE fenqu REMOVE FILE Downlog2006
ALTER DATABASE fenqu REMOVE FILEGROUP [FG_DownLog2006]
--增加新的文件组
ALTER DATABASE fenqu ADD FILEGROUP [FG_DownLog2010]
--再增加新的文件在新增的文件组中
ALTER DATABASE fenqu
ADD FILE (NAME = N'Downlog2010',FILENAME = N'F:\Downlog2010.ndf')
TO FILEGROUP [FG_DownLog2010]
--修改分区方案,增加一个新的分区
alter PARTITION SCHEME PS_ByDate next used [FG_DownLog2010]
--为了新的分区进行新的切割
alter partition function PF_RangeByDate() split range('20100101')
http://msdn.microsoft.com/zh-cn/library/ms345146.aspx
http://msdn.microsoft.com/zh-cn/library/ms345146.aspx
http://blog.csdn.net/panys/archive/2007/11/13/1882481.aspx