SQLSERVER将一个文件组的数据移动到另一个文件组
SQLSERVER将一个文件组的数据移动到另一个文件组 有经验的大侠可以直接忽视这篇文章~ 这个问题有经验的人都知道怎麽做,因为我们公司的数据量不大没有这个需求,也不知道怎麽做实验 今天求助了QQ群里的菠萝大侠,终于知道怎麽做了 我自己会把不明白的问题一
SQLSERVER将一个文件组的数据移动到另一个文件组
有经验的大侠可以直接忽视这篇文章~
这个问题有经验的人都知道怎麽做,因为我们公司的数据量不大没有这个需求,也不知道怎麽做实验
今天求助了QQ群里的菠萝大侠,终于知道怎麽做了
我自己会把不明白的问题一定会问到底的,之前在论坛问过这个弱弱的问题,但是由于太弱了,人家随便答复了一下
然后斑竹就标记为答案了~
移动数据:
1、有表分区
2、没有表分区
我这里只讨论没有表分区的情况,表分区的情况还不是很明白
例子
比如:你有三个文件组,其中一个是主文件组
测试脚本:
1 USE master 2 GO 3 4 5 IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test')) 6 DROP DATABASE [Test] 7 8 --1.创建数据库 9 CREATE DATABASE [Test] 10 GO 11 12 USE [Test] 13 GO 14 15 16 --2.创建文件组 17 ALTER DATABASE [Test] 18 ADD FILEGROUP [FG_Test_Id_01] 19 20 ALTER DATABASE [Test] 21 ADD FILEGROUP [FG_Test_Id_02] 22 23 24 25 --3.创建文件 26 ALTER DATABASE [Test] 27 ADD FILE 28 (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB ) 29 TO FILEGROUP [FG_Test_Id_01]; 30 31 ALTER DATABASE [Test] 32 ADD FILE 33 (NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB ) 34 TO FILEGROUP [FG_Test_Id_02]; 35 36 37 --4.创建表,这个表的数据存放在[FG_Test_Id_01] 文件组上 38 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 39 GO 40 41 42 --5.插入数据 43 INSERT INTO [dbo].[aa] 44 SELECT 1,REPLICATE('s',3000) 45 GO 500 46 47 48 --6.查询数据 49 SELECT * FROM [dbo].[aa] 50 51 52 --7.创建聚集索引在[FG_Test_Id_02]文件组上 53 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02] 54 GO 55 56 57 --8.我们查看一下文件组的逻辑文件名 58 EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname 59 60 61 62 --9.收缩一下FG_Test_Id_01文件组文件 63 DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1) 64 65 66 --10.你可以选择drop掉聚集索引,也可以选择不drop掉聚集索引 67 DROP INDEX PK_ID ON [dbo].[aa] 68 69 USE master 70 GO 71 DROP DATABASE [Test]
上面的脚本虽然简单,但是隐藏了非常多的知识点
知识点1:创建了两个文件组,现在数据库有三个文件组,包括主文件组,当你不指定任何参数的时候默认创建出来的数据文件是1MB大小
知识点2:插入数据,因为表是创建在[FG_Test_Id_01]文件组上,所以数据都会放在E:\FG_TestUnique_Id_01_data.ndf
1 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 2 GO
知识点3:创建聚集索引,其实这句话里面包含了几个动作,在E:\FG_TestUnique_Id_02_data.ndf文件上分配页面,并把aa表的数据
放进去E:\FG_TestUnique_Id_02_data.ndf文件,其实这里聚集索引成为了移动数据的中介,我在
SQLSERVER聚集索引与非聚集索引的再次研究(上)文章写到:聚集索引叶子节点就是数据,我们把聚集索引(一定要是聚集索引,非聚集索引不是)
建立在E:\FG_TestUnique_Id_02_data.ndf文件上实际上就是把数据页面和聚集索引页面移动到E:\FG_TestUnique_Id_02_data.ndf文件里
因为SQLSERVER是没有 ALTER TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 这种语法上
就是说你一旦建表并且表中已经有数据之后,如果你要移动表数据,只能通过聚集索引这个中介来移动表数据
1 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02] 2 GO
从下图可以看出数据都已经移动到E:\FG_TestUnique_Id_02_data.ndf文件上
知识点4:为什麽要加上WITH(ONLINE=ON)??如果你的应用是不能停机的话,加上WITH(ONLINE=ON)就可以在线的创建索引
详情参考:CREATE INDEX (Transact-SQL)
1 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02] 2 GO
知识点5:为什麽要收缩[FG_Test_Id_01]文件组文件E:\FG_TestUnique_Id_01_data.ndf
因为数据已经移动到E:\FG_TestUnique_Id_02_data.ndf文件上了,既然已经移到E:\FG_TestUnique_Id_02_data.ndf文件上
为什麽E:\FG_TestUnique_Id_01_data.ndf文件还显示5MB大小??
大家可以看一下这篇文章:聚集索引表插入数据和删除数据的方式是怎样的
我delete了数据,SQLSERVER却没有完全释放空间,其实这里移动数据到别的文件/文件组相当于delete了数据了
但是SQLSERVER并没有释放这些空间,所以我需要收缩一下FG_Test_Id_01文件组文件
1 --9.收缩一下FG_Test_Id_01文件组文件 2 DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)
知识点6:DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)
SHRINKFILE的单位是MB,上面的语句就是收缩到1MB大小,其实这里如果E:\FG_TestUnique_Id_01_data.ndf文件有数据的话
并且需要占用2MB大小的空间,那么您使用DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)这句话只能收缩到2MB大小
并不会收缩到1MB大小的,因为这些数据需要占用空间,你怎麽收缩都收缩不了的,不信的话您们可以测试一下
DBCC SHRINKFILE (Transact-SQL)
user database的initial size和dbcc shrinkfile
总结
虽然移动数据的动作比较简单,但是知识点挺多的,有些人只知道怎麽做,不知道为什么我觉得这样不好
寻根问底是我的特性o(∩_∩)o 哈哈
注意:移动数据只能一张表一张表的移动,如果表里预先已经有聚集索引,需要先drop掉
移动之前查一下表中的数据在哪个文件组中
1 USE [Northwind] 2 GO 3 EXEC [sys].[sp_help] @objname = N'[dbo].[Categories]' -- nvarchar(776)
如有不对的地方,欢迎大家拍砖o(∩_∩)o
2014-1-19 补充:
有表分区的方法
大概有三种
先创建新的数据文件,文件组,分区方案,分区函数
例如创建三个新的数据文件和文件组,分区方案和分区函数对于这三个新的数据文件和文件组
旧表:无论已经分区或者还没有分区都适用下面三种方法:
方法一:建立中间表(新表),中间表建立在新的分区方案上,然后用insert into 新表 select * from 旧表的方法,插入完毕之后
drop掉旧表
方法二:建立中间表,中间表建立在新的分区方案上,然后用switch to,因为新分区方案有三个分区
那么,switch to只能够将表的全部数据切换到其中一个分区
USE Sales GO ALTER TABLE 旧表 SWITCH PARTITION 1 TO 新表 PARTITION 1 GO
或者
USE Sales GO ALTER TABLE 旧表 SWITCH PARTITION 1 TO 新表 PARTITION 2 GO
或者
USE Sales GO ALTER TABLE 旧表 SWITCH PARTITION 1 TO 新表 PARTITION 3 GO
上面的三条语句就会把旧表的所有数据移动到新表的某个分区,至于移动到哪个分区由最后那个数字来指定PARTITION 要移动到的分区的数字
完成后drop掉旧表
方法三:drop掉旧表的聚集索引(如果有),然后在旧表上创建一个聚集索引,创建聚集索引的时候指定新的分区方案
这样就会把表数据移动到新分区方案,即新的数据文件里,这个方法跟没有表分区的数据移动是一样的
参考文章:http://blog.csdn.net/smallfools/article/details/4930810
删除文件和删除文件组
删除失效文件组 (SQL Server)
--Transact-SQL USE master; GO ALTER DATABASE AdventureWorks2012 REMOVE FILE test1dat3 ; ALTER DATABASE AdventureWorks2012 REMOVE FILE test1dat4 ; GO --Transact-SQL USE master; GO ALTER DATABASE AdventureWorks2012 REMOVE FILEGROUP Test1FG1 ; GO
下一篇: 有关 list 内存释放的小技巧
推荐阅读
-
SQLServer中数据库文件的存放方式,文件和文件组
-
SQLServer中数据库文件的存放方式,文件和文件组
-
Python将一个CSV文件里的数据追加到另一个CSV文件的方法
-
python移动文件:将一个文件夹中的特定后缀名的图片移动到另一个文件夹
-
Python将一个CSV文件里的数据追加到另一个CSV文件的方法
-
SQLSERVER将一个文件组的数据移动到另一个文件组
-
SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组
-
SQLSERVER将一个文件组的数据移动到另一个文件组
-
SQLServer中数据库文件的存放方式,文件和文件组
-
SQLServer中数据库文件的存放方式,文件和文件组