After SQL Server moves data to another filegroup, empties the filegroup and deletes the filegroup

The case of each physical file (data file) corresponding to a filegroup (one-to-one)

What if I move the data to another filegroup and don't want the empty filegroup?

The script is similar to the previous article.

Use master go IF EXISTS (SELECT * from sys.[    
       
Databases] WHERE [database_id]=db_id (' Test ')] DROP the database [test]--1. Creating databases Create DB [Test] Go   
       
Use [test] go--2. Create filegroup ALTER DATABASE [Test] ADD FILEGROUP [fg_test_id_01]   
ALTER DATABASE [test] ADD FILEGROUP [fg_test_id_02]--3. Create a file 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 ', siz   
       
       
E = 1MB, filegrowth = 1MB) to FILEGROUP [fg_test_id_02];   
       
       
--4. Create table where the data for this table is placed on the [FG_TEST_ID_01] filegroup CREATE TABLE AA (Id INT, CNAME NVARCHAR (4000)) on [fg_test_id_01] Go --5. Inserts data insert into [dbo].   
[AA] SELECT 1,replicate (' s', 3000 go 500--6. Query data SELECT * FROM [dbo]. [AA]--7. Creates a clustered index on the [FG_TEST_ID_02] filegroup, create CLUSTERED index pk_id on [dbo]. [AA] ([id]) with (Online=on) on [fg_test_id_02] go--8. Let's look at the logical file name of the filegroup, EXEC [SYS]. [sp_helpdb] @dbname = test-sysname--9. Remove fg_test_id_01 filegroup ALTER DATABASE TEST Remove file fg_t Estunique_id_01_data

When you move data to a filegroup [fg_test_id_02], there is no data in the filegroup [fg_test_id_01]. Use the following script to view

--database file, size, and already used space use [Test]--to view the usage space of the current database, automatic growth size, database file location Go Set NOCOUNT on CREATE TABLE #Data (  Fileid int NOT NULL, [filegroupid] int is not NULL, totalextents int is not NULL, usedextents int not  NULL, [FileName] sysname NOT NULL, [FilePath] nvarchar (max) is not NULL, [FileGroup] varchar (max) NULL) CREATE table #Results (db sysname null, FileType varchar (4) not NULL, [Fileg roup] sysname NOT NULL, [FileName] sysname isn't null, TOTALMB numeric (18,2) NOT NULL, USEDMB num   
       
Eric (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 Nume  Ric (18,5) not NULL, Status int is 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) S   
            Elect 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 (), [Fi   
       
Legroup] 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 W HEN r.filegroup = ' Log ' Then ' n/a ' ELSE r.filegroup end ' file group ', r.filename as "Logical file name", R.T OTALMB as "total size (MB)", R.USEDMB as "Used (MB)", r.pctused as "Used (%)", R.filepath as "File name", r.fi   
Leid 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 in R.fileid = S.fileid ORDER by 1,2,3,4,5 DROP table #Data drop table #Results drop table #Log