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 go11 12 use [test]13 go14 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 file28 (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 file33 (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 go40 41 42 --5.插入数据43 insert into [dbo].[aa]44 select 1,replicate('s',3000)45 go 50046 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 go55 56 57 --8.我们查看一下文件组的逻辑文件名58 exec [sys].[sp_helpdb] @dbname = test -- sysname59 65 66 --9.移除fg_test_id_01文件组67 alter database test68 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 #data36 set #data.filegroup = sysfilegroups.groupname37 from #data, sysfilegroups38 where #data.filegroupid = sysfilegroups.groupid39 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 fileid50 from #data51 order by --1,252 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 fileid67 from #log l , master.dbo.sysaltfiles f , dbo.sysfiles s68 where f.dbid = db_id()69 and (s.status & 0x40) 070 and s.fileid = f.fileid71 and l.db = db_name()72 73 select r.db as database,74 r.filetype as file type,75 case76 when r.filegroup = 'log' then 'n/a'77 else r.filegroup78 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 null86 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 r90 inner join dbo.sysfiles s91 on r.fileid = s.fileid92 order by 1,2,3,4,593 94 drop table #data95 drop table #results96 drop table #log
view code
使用下面的sql语句移除文件组[fg_test_id_01]就可以了
5 --9.移除fg_test_id_01文件组6 alter database test7 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 60010 11 12 --3.查询数据13 select * from [dbo].[aa]14 15 16 17 18 --4.我们查看一下文件组的逻辑文件名19 exec [sys].[sp_helpdb] @dbname = test120 -- sysname21 select db_name(database_id) as databasename ,22 name as logical_name ,23 physical_name ,24 ( size * 8 ) / 1024 sizemb25 from sys.master_files26 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.ndf6 alter database test17 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 #data36 set #data.filegroup = sysfilegroups.groupname37 from #data, sysfilegroups38 where #data.filegroupid = sysfilegroups.groupid39 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 fileid50 from #data51 order by --1,252 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 fileid67 from #log l , master.dbo.sysaltfiles f , dbo.sysfiles s68 where f.dbid = db_id()69 and (s.status & 0x40) 070 and s.fileid = f.fileid71 and l.db = db_name()72 73 select r.db as database,74 r.filetype as file type,75 case76 when r.filegroup = 'log' then 'n/a'77 else r.filegroup78 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 null86 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 r90 inner join dbo.sysfiles s91 on r.fileid = s.fileid92 order by 1,2,3,4,593 94 drop table #data95 drop table #results96 drop table #log
view 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 go13 14 use [test]15 go16 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 file29 (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 file34 (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 go44 45 46 --5.插入数据47 insert into [dbo].[aa]48 select 1,replicate('s',3000)49 go 100050 51 52 --6.查询数据53 select * from [dbo].[aa]54 55 56 57 58 --7.我们查看一下文件组的逻辑文件名59 exec [sys].[sp_helpdb] @dbname = test -- sysname60 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.ndf68 alter database test69 remove file fg_testunique_id_02_data70 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