SQLSERVER将数据移动到另一个文件组之后清空文件组并删除文件组

SQLSERVER将数据移动到另一个文件组之后清空文件组并删除文件组

之前写过一篇文章：SQLSERVER将一个文件组的数据移动到另一个文件组

---

每个物理文件（数据文件）对应一个文件组的情况（一对一）

如果我把数据移到另一个文件组了，不想要这个已经清空的文件组了，怎麽做？

脚本跟之前那篇文章差不多

USE master
GO


IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test'))
DROP DATABASE [Test]

--1.创建数据库
CREATE DATABASE [Test]
GO

USE [Test]
GO


--2.创建文件组
ALTER DATABASE [Test]
ADD FILEGROUP [FG_Test_Id_01]

ALTER DATABASE [Test]
ADD FILEGROUP [FG_Test_Id_02]



--3.创建文件
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_01];

ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_02];


--4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 
GO


--5.插入数据
INSERT INTO [dbo].[aa]
SELECT 1,REPLICATE('s',3000)
GO 500


--6.查询数据
SELECT * FROM [dbo].[aa]


--7.创建聚集索引在[FG_Test_Id_02]文件组上
CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02]
GO


--8.我们查看一下文件组的逻辑文件名
EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname


--9.移除FG_Test_Id_01文件组
ALTER DATABASE TEST
REMOVE FILE FG_TestUnique_Id_01_data

当你移动数据到文件组[FG_Test_Id_02]上时，这时候文件组[FG_Test_Id_01]里面已经没有数据了
使用下面的脚本查看

--数据库文件、大小和已经使用空间
USE [Test]  --要查看的当前数据库的使用空间，自动增长大小，数据库文件位置
GO
set nocount on
create table #Data(
      FileID int NOT NULL,
      [FileGroupId] int NOT NULL,
      TotalExtents int NOT NULL,
      UsedExtents int NOT NULL,
      [FileName] sysname NOT NULL,
      [FilePath] nvarchar(MAX) NOT NULL,
      [FileGroup] varchar(MAX) NULL)

create table #Results(
      db sysname NULL ,
      FileType varchar(4) NOT NULL,
      [FileGroup] sysname not null,
      [FileName] sysname NOT NULL,
      TotalMB numeric(18,2) NOT NULL,
      UsedMB numeric(18,2) NOT NULL,
      PctUsed numeric(18,2) NULL,
      FilePath nvarchar(MAX) NULL,
      FileID int null)

create table #Log(
      db sysname NOT NULL,
      LogSize numeric(18,5) NOT NULL,
      LogUsed numeric(18,5) NOT NULL,
      Status int NOT NULL,
      [FilePath] nvarchar(MAX) NULL)

INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath])
EXEC ('DBCC showfilestats WITH NO_INFOMSGS')

update #Data
set #Data.FileGroup = sysfilegroups.groupname
from #Data, sysfilegroups
where #Data.FileGroupId = sysfilegroups.groupid

INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID)
SELECT DB_NAME() db,
            [FileGroup],
            'Data' FileType,
            [FileName],
            TotalExtents * 64./1024. TotalMB,
            UsedExtents *64./1024 UsedMB,
            UsedExtents*100. /TotalExtents  UsedPct,
            [FilePath],
            FileID
FROM #Data
order BY --1,2
DB_NAME(), [FileGroup]

insert #Log (db,LogSize,LogUsed,Status)
exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ')

insert #Results(db, [FileGroup], FileType, [FileName],  TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
            'Log' [FileGroup],
            'Log' FileType,
            s.[name] [FileName],
            s.Size/128. as LogSize ,
            FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
            ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct,
            s.FileName FilePath,
            s.FileID FileID
      from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
      where f.dbid = DB_ID()
      and (s.status & 0x40) <> 0
      and s.FileID = f.FileID
      and l.db = DB_NAME()

SELECT r.db AS "Database",
r.FileType AS "File type",
CASE
     WHEN r.FileGroup = 'Log' Then 'N/A'
     ELSE r.FileGroup
END "File group",
r.FileName AS "Logical file name",
r.TotalMB AS "Total size (MB)",
r.UsedMB AS "Used (MB)",
r.PctUsed AS "Used (%)",
r.FilePath AS "File name",
r.FileID AS "File ID",
CASE WHEN s.maxsize = -1 THEN null
    ELSE CONVERT(decimal(18,2), s.maxsize /128.)
END "Max. size (MB)",
CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log

 

使用下面的SQL语句移除文件组[FG_Test_Id_01]就可以了

 
--9.移除FG_Test_Id_01文件组
ALTER DATABASE TEST
REMOVE FILE FG_TestUnique_Id_01_data

此时就只剩下主文件组和[FG_Test_Id_02]文件组了

注意：如果不使用聚集索引来移动文件组[FG_Test_Id_01]上的数据到文件组[FG_Test_Id_02]

--4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 
GO

直接使用下面SQL语句来收缩文件会报错

-收缩一下FG_Test_Id_01文件组文件
DBCC SHRINKFILE(FG_TestUnique_Id_01_data,EMPTYFILE)

报错内容

DBCC SHRINKFILE: 无法移动堆页 3:515。
消息 2555，级别 16，状态 1，第 1 行
无法将文件 "FG_TestUnique_Id_01_data" 的所有内容移到其他位置，以完成清空文件操作。
语句已终止。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
消息 1105，级别 17，状态 2，第 1 行
无法为数据库 'Test' 中的对象 'dbo.aa' 分配空间，因为 'FG_Test_Id_01' 文件组已满。请删除不需要的文件、删除文件组中的对象、将其他文件添加到文件组或为文件组中的现有文件启用自动增长，以便增加可用磁盘空间。

因为文件组[FG_Test_Id_01]里还有数据，不能清空

---

两个物理文件（数据文件）对应一个文件组的情况（一对多）

上面的情况是每个物理文件（数据文件）对应一个文件组的情况

下面这种情况是两个物理文件（数据文件）对于一个文件组的情况

一对一的情况使用聚集索引到不同文件组来移动数据，而一对多的情况使用DBCC SHRINKFILE

创建数据库

test1和test2这两个数据文件归属于主文件组primary，而数据文件test1最大大小为6MB初始大小为5MB

test2数据文件最大大小没有限制

使用下面脚本添加数据到主文件组

--1.创建表，这个表的数据存放在主文件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) 
GO


--2.插入数据
INSERT INTO [dbo].[aa]
SELECT 1,REPLICATE('s',3000)
GO 600


--3.查询数据
SELECT * FROM [dbo].[aa]




--4.我们查看一下文件组的逻辑文件名
EXEC [sys].[sp_helpdb] @dbname = TEST1
 -- sysname
SELECT  DB_NAME(database_id) AS DatabaseName ,
        Name AS Logical_Name ,
        Physical_Name ,
        ( size * 8 ) / 1024 SizeMB
FROM    sys.master_files
WHERE   DB_NAME(database_id) = 'Test1'

因为第一个数据文件的最大大小限制，所以有一部分数据插入到了test2.ndf

 

 

现在修改test1数据文件的最大大小限制为20MB

相关SQL

ALTER DATABASE [Test1] MODIFY FILE(name='Test1',SIZE=5MB, filegrowth=1MB, MAXSIZE=20MB)

 

执行下面的SQL语句

--5.收缩文件
DBCC SHRINKFILE(test2,EMPTYFILE)


--6.移除test2数据文件test2.ndf
ALTER DATABASE TEST1
REMOVE FILE test2

在执行第五条语句的时候，执行下面脚本

--数据库文件、大小和已经使用空间
USE [Test1]  --要查看的当前数据库的使用空间，自动增长大小，数据库文件位置
GO
set nocount on
create table #Data(
      FileID int NOT NULL,
      [FileGroupId] int NOT NULL,
      TotalExtents int NOT NULL,
      UsedExtents int NOT NULL,
      [FileName] sysname NOT NULL,
      [FilePath] nvarchar(MAX) NOT NULL,
      [FileGroup] varchar(MAX) NULL)

create table #Results(
      db sysname NULL ,
      FileType varchar(4) NOT NULL,
      [FileGroup] sysname not null,
      [FileName] sysname NOT NULL,
      TotalMB numeric(18,2) NOT NULL,
      UsedMB numeric(18,2) NOT NULL,
      PctUsed numeric(18,2) NULL,
      FilePath nvarchar(MAX) NULL,
      FileID int null)

create table #Log(
      db sysname NOT NULL,
      LogSize numeric(18,5) NOT NULL,
      LogUsed numeric(18,5) NOT NULL,
      Status int NOT NULL,
      [FilePath] nvarchar(MAX) NULL)

INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath])
EXEC ('DBCC showfilestats WITH NO_INFOMSGS')

update #Data
set #Data.FileGroup = sysfilegroups.groupname
from #Data, sysfilegroups
where #Data.FileGroupId = sysfilegroups.groupid

INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID)
SELECT DB_NAME() db,
            [FileGroup],
            'Data' FileType,
            [FileName],
            TotalExtents * 64./1024. TotalMB,
            UsedExtents *64./1024 UsedMB,
            UsedExtents*100. /TotalExtents  UsedPct,
            [FilePath],
            FileID
FROM #Data
order BY --1,2
DB_NAME(), [FileGroup]

insert #Log (db,LogSize,LogUsed,Status)
exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ')

insert #Results(db, [FileGroup], FileType, [FileName],  TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
            'Log' [FileGroup],
            'Log' FileType,
            s.[name] [FileName],
            s.Size/128. as LogSize ,
            FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
            ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct,
            s.FileName FilePath,
            s.FileID FileID
      from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
      where f.dbid = DB_ID()
      and (s.status & 0x40) <> 0
      and s.FileID = f.FileID
      and l.db = DB_NAME()

SELECT r.db AS "Database",
r.FileType AS "File type",
CASE
     WHEN r.FileGroup = 'Log' Then 'N/A'
     ELSE r.FileGroup
END "File group",
r.FileName AS "Logical file name",
r.TotalMB AS "Total size (MB)",
r.UsedMB AS "Used (MB)",
r.PctUsed AS "Used (%)",
r.FilePath AS "File name",
r.FileID AS "File ID",
CASE WHEN s.maxsize = -1 THEN null
    ELSE CONVERT(decimal(18,2), s.maxsize /128.)
END "Max. size (MB)",
CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log

你会发现

数据都移动到了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补充：

这个实验的测试脚本和结果

USE master
GO

--DROP DATABASE [Test]


IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test'))
DROP DATABASE [Test]

--1.创建数据库
CREATE DATABASE [Test]
GO

USE [Test]
GO
--2.创建文件组
ALTER DATABASE [Test]
ADD FILEGROUP [FG_Test_Id_01]

--3.创建文件
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_01];
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_01];

--4.创建表，这个表的数据存放在[FG_Test_Id_02] 文件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 
GO

--5.插入数据
INSERT INTO [dbo].[aa]
SELECT 1,REPLICATE('s',3000)
GO 1000

--6.查询数据
SELECT * FROM [dbo].[aa]

--7.我们查看一下文件组的逻辑文件名
EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname

--8.收缩文件
DBCC SHRINKFILE(FG_TestUnique_Id_02_data,EMPTYFILE)

--9.移除FG_TestUnique_Id_02_data数据文件FG_TestUnique_Id_02_data.ndf
ALTER DATABASE TEST
REMOVE FILE FG_TestUnique_Id_02_data

--10.查询数据
SELECT * FROM [dbo].[aa]
SELECT COUNT(*) FROM [dbo].[aa]

 

 

 

数据没有丢失

答案：

FG_TestUnique_Id_02_data.ndf里的数据会移动到FG_TestUnique_Id_01_data.ndf，不会移动到Test.mdf

因为DBCC SHRINKFILE只能在同一文件组内移动数据，而mdf文件属于主文件组primary

 

表分区 

分区表没有数据然后文件组依赖的文件删除了 居然没有报错
然后插入数据到分区表报错没有分配文件 

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o