SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组 之前写过一篇文章： SQLSERVER将一个文件组的数据移动到另一个文件组 每个物理文件（数据文件）对应一个文件组的情况（一对一） 如果我把数据移到另一个文件组了，不想要这个已经清空的文件组了
SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组
之前写过一篇文章： SQLSERVER将一个文件组的数据移动到另一个文件组
每个物理文件（数据文件）对应一个文件组的情况（一对一）
如果我把数据移到另一个文件组了，不想要这个已经清空的文件组了，怎麽做？
脚本跟之前那篇文章差不多
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 65 66 --9 .移除FG_Test_Id_01文件组 67 ALTER DATABASE TEST 68 REMOVE FILE FG_TestUnique_Id_01_data
当你移动数据到文件组[FG_Test_Id_02]上时，这时候文件组[FG_Test_Id_01]里面已经没有数据了
使用下面的脚本查看
1 -- 数据库文件、大小和已经使用空间 2 USE [ Test ] -- 要查看的当前数据库的使用空间，自动增长大小，数据库文件位置 3 GO 4 set nocount on 5 create table #Data( 6 FileID int NOT NULL , 7 [ FileGroupId ] int NOT NULL , 8 TotalExtents int NOT NULL , 9 UsedExtents int NOT NULL , 10 [ FileName ] sysname NOT NULL , 11 [ FilePath ] nvarchar ( MAX ) NOT NULL , 12 [ FileGroup ] varchar ( MAX ) NULL ) 13 14 create table #Results( 15 db sysname NULL , 16 FileType varchar ( 4 ) NOT NULL , 17 [ FileGroup ] sysname not null , 18 [ FileName ] sysname NOT NULL , 19 TotalMB numeric( 18 , 2 ) NOT NULL , 20 UsedMB numeric( 18 , 2 ) NOT NULL , 21 PctUsed numeric( 18 , 2 ) NULL , 22 FilePath nvarchar ( MAX ) NULL , 23 FileID int null ) 24 25 create table # Log ( 26 db sysname NOT NULL , 27 LogSize numeric( 18 , 5 ) NOT NULL , 28 LogUsed numeric( 18 , 5 ) NOT NULL , 29 Status int NOT NULL , 30 [ FilePath ] nvarchar ( MAX ) NULL ) 31 32 INSERT #Data (FileID, [ FileGroupId ] , TotalExtents, UsedExtents, [ FileName ] , [ FilePath ] ) 33 EXEC ( ' DBCC showfilestats WITH NO_INFOMSGS ' ) 34 35 update #Data 36 set #Data.FileGroup = sysfilegroups.groupname 37 from #Data, sysfilegroups 38 where #Data.FileGroupId = sysfilegroups.groupid 39 40 INSERT INTO #Results (db, [ FileGroup ] , FileType, [ FileName ] , TotalMB, UsedMB, PctUsed, FilePath, FileID) 41 SELECT DB_NAME () db, 42 [ FileGroup ] , 43 ' Data ' FileType, 44 [ FileName ] , 45 TotalExtents * 64 . / 1024 . TotalMB, 46 UsedExtents * 64 . / 1024 UsedMB, 47 UsedExtents * 100 . / TotalExtents UsedPct, 48 [ FilePath ] , 49 FileID 50 FROM #Data 51 order BY -- 1,2 52 DB_NAME (), [ FileGroup ] 53 54 insert # Log (db,LogSize,LogUsed,Status) 55 exec ( ' dbcc sqlperf(logspace) WITH NO_INFOMSGS ' ) 56 57 insert #Results(db, [ FileGroup ] , FileType, [ FileName ] , TotalMB,UsedMB, PctUsed, FilePath, FileID) 58 select DB_NAME () db, 59 ' Log ' [ FileGroup ] , 60 ' Log ' FileType, 61 s. [ name ] [ FileName ] , 62 s.Size / 128 . as LogSize , 63 FILEPROPERTY (s.name, ' spaceused ' ) / 8.00 / 16.00 As LogUsedSpace, 64 (( FILEPROPERTY (s.name, ' spaceused ' ) / 8.00 / 16.00 ) * 100 ) / (s.Size / 128 .) UsedPct, 65 s.FileName FilePath, 66 s.FileID FileID 67 from # Log l , master.dbo.sysaltfiles f , dbo.sysfiles s 68 where f.dbid = DB_ID () 69 and (s.status & 0x40 ) 0 70 and s.FileID = f.FileID 71 and l.db = DB_NAME () 72 73 SELECT r.db AS " Database ", 74 r.FileType AS " File type", 75 CASE 76 WHEN r.FileGroup = ' Log ' Then ' N/A ' 77 ELSE r.FileGroup 78 END " File group ", 79 r.FileName AS "Logical file name", 80 r.TotalMB AS "Total size (MB)", 81 r.UsedMB AS "Used (MB)", 82 r.PctUsed AS "Used ( % )", 83 r.FilePath AS " File name", 84 r.FileID AS " File ID", 85 CASE WHEN s.maxsize = - 1 THEN null 86 ELSE CONVERT ( decimal ( 18 , 2 ), s.maxsize / 128 .) 87 END " Max . size (MB)", 88 CONVERT ( decimal ( 18 , 2 ), s.growth / 128 .) "Autogrowth increment (MB)" 89 FROM #Results r 90 INNER JOIN dbo.sysfiles s 91 ON r.FileID = s.FileID 92 ORDER BY 1 , 2 , 3 , 4 , 5 93 94 DROP TABLE #Data 95 DROP TABLE #Results 96 DROP TABLE # LogView Code
使用下面的SQL语句移除文件组[FG_Test_Id_01]就可以了
5 --9 .移除FG_Test_Id_01文件组 6 ALTER DATABASE TEST 7 REMOVE FILE FG_TestUnique_Id_01_data
此时就只剩下主文件组和[FG_Test_Id_02]文件组了
注意：如果不使用聚集索引来移动文件组[FG_Test_Id_01]上的数据到文件组[FG_Test_Id_02]
1 -- 4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上 2 CREATE TABLE aa(id INT ,cname NVARCHAR ( 4000 )) ON [ FG_Test_Id_01 ] 3 GO
直接使用下面SQL语句来收缩文件会报错
1 - 收缩一下FG_Test_Id_01文件组文件 2 DBCC SHRINKFILE(FG_TestUnique_Id_01_data,EMPTYFILE)
报错内容
1 DBCC SHRINKFILE: 无法移动堆页 3 : 515 。 2 消息 2555 ，级别 16 ，状态 1 ，第 1 行 3 无法将文件 "FG_TestUnique_Id_01_data" 的所有内容移到其他位置，以完成清空文件操作。 4 语句已终止。 5 DBCC 执行完毕。如果 DBCC输出了错误信息，请与系统管理员联系。 6 消息 1105 ，级别 17 ，状态 2 ，第 1 行 7 无法为数据库 ' Test ' 中的对象 ' dbo.aa ' 分配空间，因为 ' FG_Test_Id_01 ' 文件组已满。请删除不需要的文件、删除文件组中的对象、将其他文件添加到文件组或为文件组中的现有文件启用自动增长，以便增加可用磁盘空间。
因为文件组[FG_Test_Id_01]里还有数据，不能清空
两个物理文件（数据文件）对应一个文件组的情况（一对多）
上面的情况是每个物理文件（数据文件）对应一个文件组的情况
下面这种情况是两个物理文件（数据文件）对于一个文件组的情况
一对一的情况使用聚集索引里移动数据，而一对一的情况使用DBCC SHRINKFILE
创建数据库
test1和test2这两个数据文件归属于主文件组primary，而数据文件test1最大大小为6MB初始大小为5MB
test2数据文件最大大小没有限制
使用下面脚本添加数据到主文件组
1 -- 1.创建表，这个表的数据存放在主文件组上 2 CREATE TABLE aa(id INT ,cname NVARCHAR ( 4000 )) 3 GO 4 5 6 -- 2.插入数据 7 INSERT INTO [ dbo ] . [ aa ] 8 SELECT 1 , REPLICATE ( ' s ' , 3000 ) 9 GO 600 10 11 12 -- 3.查询数据 13 SELECT * FROM [ dbo ] . [ aa ] 14 15 16 17 18 -- 4.我们查看一下文件组的逻辑文件名 19 EXEC [ sys ] . [ sp_helpdb ] @dbname = TEST1 20 -- sysname 21 SELECT DB_NAME (database_id) AS DatabaseName , 22 Name AS Logical_Name , 23 Physical_Name , 24 ( size * 8 ) / 1024 SizeMB 25 FROM sys.master_files 26 WHERE DB_NAME (database_id) = ' Test1 '
因为第一个数据文件的最大大小限制，所以有一部分数据插入到了test2.ndf
现在修改test1数据文件的最大大小限制为20MB
相关SQL
1 ALTER DATABASE [ Test1 ] MODIFY FILE (name = ' Test1 ' ,SIZE = 5MB, filegrowth = 1MB, MAXSIZE = 20MB)View Code
执行下面的SQL语句
1 -- 5.收缩文件 2 DBCC SHRINKFILE(test2,EMPTYFILE) 3 4 5 -- 6.移除test2数据文件test2.ndf 6 ALTER DATABASE TEST1 7 REMOVE FILE test2
在执行第五条语句的时候，执行下面脚本
1 -- 数据库文件、大小和已经使用空间 2 USE [ Test1 ] -- 要查看的当前数据库的使用空间，自动增长大小，数据库文件位置 3 GO 4 set nocount on 5 create table #Data( 6 FileID int NOT NULL , 7 [ FileGroupId ] int NOT NULL , 8 TotalExtents int NOT NULL , 9 UsedExtents int NOT NULL , 10 [ FileName ] sysname NOT NULL , 11 [ FilePath ] nvarchar ( MAX ) NOT NULL , 12 [ FileGroup ] varchar ( MAX ) NULL ) 13 14 create table #Results( 15 db sysname NULL , 16 FileType varchar ( 4 ) NOT NULL , 17 [ FileGroup ] sysname not null , 18 [ FileName ] sysname NOT NULL , 19 TotalMB numeric( 18 , 2 ) NOT NULL , 20 UsedMB numeric( 18 , 2 ) NOT NULL , 21 PctUsed numeric( 18 , 2 ) NULL , 22 FilePath nvarchar ( MAX ) NULL , 23 FileID int null ) 24 25 create table # Log ( 26 db sysname NOT NULL , 27 LogSize numeric( 18 , 5 ) NOT NULL , 28 LogUsed numeric( 18 , 5 ) NOT NULL , 29 Status int NOT NULL , 30 [ FilePath ] nvarchar ( MAX ) NULL ) 31 32 INSERT #Data (FileID, [ FileGroupId ] , TotalExtents, UsedExtents, [ FileName ] , [ FilePath ] ) 33 EXEC ( ' DBCC showfilestats WITH NO_INFOMSGS ' ) 34 35 update #Data 36 set #Data.FileGroup = sysfilegroups.groupname 37 from #Data, sysfilegroups 38 where #Data.FileGroupId = sysfilegroups.groupid 39 40 INSERT INTO #Results (db, [ FileGroup ] , FileType, [ FileName ] , TotalMB, UsedMB, PctUsed, FilePath, FileID) 41 SELECT DB_NAME () db, 42 [ FileGroup ] , 43 ' Data ' FileType, 44 [ FileName ] , 45 TotalExtents * 64 . / 1024 . TotalMB, 46 UsedExtents * 64 . / 1024 UsedMB, 47 UsedExtents * 100 . / TotalExtents UsedPct, 48 [ FilePath ] , 49 FileID 50 FROM #Data 51 order BY -- 1,2 52 DB_NAME (), [ FileGroup ] 53 54 insert # Log (db,LogSize,LogUsed,Status) 55 exec ( ' dbcc sqlperf(logspace) WITH NO_INFOMSGS ' ) 56 57 insert #Results(db, [ FileGroup ] , FileType, [ FileName ] , TotalMB,UsedMB, PctUsed, FilePath, FileID) 58 select DB_NAME () db, 59 ' Log ' [ FileGroup ] , 60 ' Log ' FileType, 61 s. [ name ] [ FileName ] , 62 s.Size / 128 . as LogSize , 63 FILEPROPERTY (s.name, ' spaceused ' ) / 8.00 / 16.00 As LogUsedSpace, 64 (( FILEPROPERTY (s.name, ' spaceused ' ) / 8.00 / 16.00 ) * 100 ) / (s.Size / 128 .) UsedPct, 65 s.FileName FilePath, 66 s.FileID FileID 67 from # Log l , master.dbo.sysaltfiles f , dbo.sysfiles s 68 where f.dbid = DB_ID () 69 and (s.status & 0x40 ) 0 70 and s.FileID = f.FileID 71 and l.db = DB_NAME () 72 73 SELECT r.db AS " Database ", 74 r.FileType AS " File type", 75 CASE 76 WHEN r.FileGroup = ' Log ' Then ' N/A ' 77 ELSE r.FileGroup 78 END " File group ", 79 r.FileName AS "Logical file name", 80 r.TotalMB AS "Total size (MB)", 81 r.UsedMB AS "Used (MB)", 82 r.PctUsed AS "Used ( % )", 83 r.FilePath AS " File name", 84 r.FileID AS " File ID", 85 CASE WHEN s.maxsize = - 1 THEN null 86 ELSE CONVERT ( decimal ( 18 , 2 ), s.maxsize / 128 .) 87 END " Max . size (MB)", 88 CONVERT ( decimal ( 18 , 2 ), s.growth / 128 .) "Autogrowth increment (MB)" 89 FROM #Results r 90 INNER JOIN dbo.sysfiles s 91 ON r.FileID = s.FileID 92 ORDER BY 1 , 2 , 3 , 4 , 5 93 94 DROP TABLE #Data 95 DROP TABLE #Results 96 DROP TABLE # LogView Code
你会发现
数据都移动到了test1.mdf里去了
执行第六条SQL语句，删除test2.ndf文件
数据没有丢失
这里关键在于 EMPTYFILE 参数 ：DBCC SHRINKFILE(test2, EMPTYFILE )
总结
这里要根据是一对多还是一对一来选择移动数据的方法
如果是一对多：使用DBCC SHRINKFILE(要移动数据的数据文件, EMPTYFILE )
如果是一对一：创建聚集索引
参考文章： [SQL]透過 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將資料移到另一個資料檔之中
大家可以做一下实验
对于同一个文件组里的多个数据文件（不一定是主文件组），
比如有有个文件组叫[FG_Test_01]，里面有两个数据文件test3.ndf和test4.ndf
test3.ndf和test4.ndf都有数据
如果我运行DBCC SHRINKFILE(test4, EMPTYFILE )，test4.ndf里的数据是否会移动到test3.ndf还是会移动到test1.mdf？？？
这个实验留给大家o(∩_∩)o
2014-1-14补充：
这个实验的测试脚本和结果
1 USE master 2 GO 3 4 -- DROP DATABASE [Test] 5 6 7 IF EXISTS ( SELECT * FROM sys. [ databases ] WHERE [ database_id ] = DB_ID ( ' Test ' )) 8 DROP DATABASE [ Test ] 9 10 -- 1.创建数据库 11 CREATE DATABASE [ Test ] 12 GO 13 14 USE [ Test ] 15 GO 16 17 18 -- 2.创建文件组 19 ALTER DATABASE [ Test ] 20 ADD FILEGROUP [ FG_Test_Id_01 ] 21 22 23 24 25 26 -- 3.创建文件 27 ALTER DATABASE [ Test ] 28 ADD FILE 29 (NAME = N ' FG_TestUnique_Id_01_data ' ,FILENAME = N ' E:\FG_TestUnique_Id_01_data.ndf ' ,SIZE = 1MB, FILEGROWTH = 1MB ) 30 TO FILEGROUP [ FG_Test_Id_01 ] ; 31 32 ALTER DATABASE [ Test ] 33 ADD FILE 34 (NAME = N ' FG_TestUnique_Id_02_data ' ,FILENAME = N ' E:\FG_TestUnique_Id_02_data.ndf ' ,SIZE = 1MB, FILEGROWTH = 1MB ) 35 TO FILEGROUP [ FG_Test_Id_01 ] ; 36 37 38 39 40 41 -- 4.创建表，这个表的数据存放在[FG_Test_Id_02] 文件组上 42 CREATE TABLE aa(id INT ,cname NVARCHAR ( 4000 )) ON [ FG_Test_Id_01 ] 43 GO 44 45 46 -- 5.插入数据 47 INSERT INTO [ dbo ] . [ aa ] 48 SELECT 1 , REPLICATE ( ' s ' , 3000 ) 49 GO 1000 50 51 52 -- 6.查询数据 53 SELECT * FROM [ dbo ] . [ aa ] 54 55 56 57 58 -- 7.我们查看一下文件组的逻辑文件名 59 EXEC [ sys ] . [ sp_helpdb ] @dbname = TEST -- sysname 60 61 62 63 -- 8.收缩文件 64 DBCC SHRINKFILE(FG_TestUnique_Id_02_data,EMPTYFILE) 65 66 67 -- 9.移除FG_TestUnique_Id_03_data数据文件FG_TestUnique_Id_03_data.ndf 68 ALTER DATABASE TEST 69 REMOVE FILE FG_TestUnique_Id_02_data 70 71 72 73 -- 10.查询数据 74 SELECT * FROM [ dbo ] . [ aa ] 75 SELECT COUNT ( * ) FROM [ dbo ] . [ aa ]View Code
数据没有丢失
答案：
FG_TestUnique_Id_02_data.ndf里的数据会移动到FG_TestUnique_Id_01_data.ndf，不会移动到Test.mdf
因为DBCC SHRINKFILE只能在同一文件组内移动数据，而mdf只能属于主文件组primary
如有不对的地方，欢迎大家拍砖o(∩_∩)o
查看更多关于SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did98183