SQLServer 移除合并文件及文件组

由于数据库文件多了，得重新规划，所以必须得删除多余的文件和文件组。本文简单测试，最终只保留primary文件组和一个主文件。

--模拟测试USE masterGO--DROP DATABASE [TestDB]CREATE DATABASE [TestDB] ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\MSSQLDateFiles\TestDB\TestDB.mdf' , SIZE = 102400KB , FILEGROWTH = 1024KB ), ( NAME = N'file', FILENAME = N'D:\MSSQLDateFiles\TestDB\file.mdf' , SIZE = 102400KB , FILEGROWTH = 1024KB ),  FILEGROUP [FG1] ( NAME = N'file1', FILENAME = N'D:\MSSQLDateFiles\TestDB\file1.ndf' , SIZE = 102400KB , FILEGROWTH = 1024KB ), ( NAME = N'file2', FILENAME = N'D:\MSSQLDateFiles\TestDB\file2.ndf' , SIZE = 102400KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log', FILENAME = N'D:\MSSQLDateFiles\TestDB\TestDB_log.ldf' , SIZE = 51200KB , FILEGROWTH = 10%)GOUSE [TestDB]GOCREATE TABLE [TestTab] (    [ID] INT IDENTITY NOT NULL,[Name] CHAR (30) DEFAULT 'TEST DATA',[Value] DECIMAL(18,4) DEFAULT 0,    [Date] DATETIME DEFAULT GETDATE ()) ON [PRIMARY]GOCREATE TABLE [TestTab2] (    [ID] INT IDENTITY NOT NULL,[Name] CHAR (30) DEFAULT 'TEST DATA',[Value] DECIMAL(18,4) DEFAULT 0,    [Date] DATETIME DEFAULT GETDATE ()) ON [FG1]GO--插入测试数据SET NOCOUNT ONINSERT INTO [TestTab] DEFAULT VALUESGO 10000INSERT INTO [TestTab2] DEFAULT VALUESGO 10000SET NOCOUNT OFF--查看表和文件组信息,文件都存储有数据了SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]  FROM sys.indexes i  INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id  INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]  WHERE o.name in( 'TestTab','TestTab2')  GO  DBCC showfilestatsGO

--现在移除文件，结果失败！文件中有数据，不能删除！USE masterGOALTER DATABASE [TestDB] REMOVE FILE [file]GO/*错误信息：Msg 5042, Level 16, State 1, Line 1The file 'file' cannot be removed because it is not empty.*/--将指定文件中的所有数据迁移到同一文件组中的其他文件USE [TestDB]goDBCC SHRINKFILE ('file', EMPTYFILE);GODBCC SHRINKFILE ('file2', EMPTYFILE);GODBCC showfilestatsGO

--再移除文件，正常移除！USE masterGOALTER DATABASE [TestDB] REMOVE FILE [file]GOALTER DATABASE [TestDB] REMOVE FILE [file2]GOUSE [TestDB]goDBCC showfilestatsGO

--若删除文件组或文件组中的唯一文件，出错！USE [TestDB]goDBCC SHRINKFILE ('file1', EMPTYFILE);GOUSE masterGOALTER DATABASE [TestDB] REMOVE FILE [file1]GOALTER DATABASE [TestDB] REMOVE FILEGROUP [FG1]GO/*错误信息：DBCC SHRINKFILE: Heap page 4:85 could not be moved.Msg 2555, Level 16, State 1, Line 1Cannot move all contents of file "file1" to other places to complete the emptyfile operation.The statement has been terminated.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Msg 1105, Level 17, State 2, Line 1Could not allocate space for object 'dbo.TestTab2' in database 'TestDB' because the 'FG1' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.Msg 5042, Level 16, State 1, Line 1The file 'file1' cannot be removed because it is not empty.Msg 5042, Level 16, State 7, Line 1The filegroup 'FG1' cannot be removed because it is not empty.*/--因此，先把文件组 [FG1] 的数据转移到文件组 [primary] 中USE [TestDB]goCREATE CLUSTERED INDEX [IX_TestTab2] ON dbo.TestTab2([ID]) ON [PRIMARY]  GO  --先移除文件，再移除文件组，完成！ALTER DATABASE [TestDB] REMOVE FILE [file1]GOALTER DATABASE [TestDB] REMOVE FILEGROUP [FG1]GO--看看最终结果！USE [TestDB]GO SELECT COUNT(*) FROM TestTabSELECT COUNT(*) FROM TestTab2GO SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]  FROM sys.indexes i  INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id  INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]  WHERE o.name in( 'TestTab','TestTab2')  GO  DBCC showfilestatsGO