之前寫過一篇文章：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

使用下面的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)

執行下面的SQL語句

1 --5.收縮文件2 DBCC SHRINKFILE(test2,EMPTYFILE)3 4 5 --6.移除test2數據文件test2.ndf6 ALTER DATABASE TEST17 REMOVE FILE test2

在執行第五條語句的時候，執行下面腳本