SQL SERVER將數據移到另一個文件組之後清空文件組並刪除文件組

每個物理文件（數據文件）對應一個文件組的情況（一對一）

如果我把數據移到另一個文件組了，不想要這個已經清空的文件組了，怎麽做？

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

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