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

（编辑：滁州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!