SQLServer 移除合并文件及文件组

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

 

--模拟测试
USE master
GO
--  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%)
GO

USE [TestDB]
GO
CREATE 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]
GO
CREATE 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 ON
INSERT INTO [TestTab] DEFAULT VALUES
GO 10000
INSERT INTO [TestTab2] DEFAULT VALUES
GO 10000
SET 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 showfilestats
GO

--现在移除文件，结果失败！文件中有数据，不能删除！
USE master
GO
ALTER DATABASE [TestDB] REMOVE FILE [file]
GO

/*错误信息：
Msg 5042, Level 16, State 1, Line 1
The file 'file' cannot be removed because it is not empty.
*/

--  将指定文件中的所有数据迁移到同一文件组中的其他文件
USE [TestDB]
go
DBCC SHRINKFILE ('file', EMPTYFILE);
GO
DBCC SHRINKFILE ('file2', EMPTYFILE);
GO
DBCC showfilestats
GO

--再移除文件，正常移除！
USE master
GO
ALTER DATABASE [TestDB] REMOVE FILE [file]
GO
ALTER DATABASE [TestDB] REMOVE FILE [file2]
GO

USE [TestDB]
go
DBCC showfilestats
GO

 

--若删除文件组或文件组中的唯一文件，出错！
USE [TestDB]
go
DBCC SHRINKFILE ('file1', EMPTYFILE);
GO
USE master
GO
ALTER DATABASE [TestDB] REMOVE FILE [file1]
GO
ALTER DATABASE [TestDB] REMOVE FILEGROUP [FG1]
GO
/*错误信息：
DBCC SHRINKFILE: Heap page 4:85 could not be moved.
Msg 2555, Level 16, State 1, Line 1
Cannot 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 1
Could 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 1
The file 'file1' cannot be removed because it is not empty.

Msg 5042, Level 16, State 7, Line 1
The filegroup 'FG1' cannot be removed because it is not empty.
*/


--因此，先把文件组 [FG1] 的数据转移到文件组 [primary] 中
USE [TestDB]
go
CREATE CLUSTERED INDEX [IX_TestTab2] ON dbo.TestTab2([ID]) ON [PRIMARY]
GO
--先移除文件，再移除文件组，完成！
ALTER DATABASE [TestDB] REMOVE FILE [file1]
GO
ALTER DATABASE [TestDB] REMOVE FILEGROUP [FG1]
GO


--看看最终结果！
USE [TestDB]
GO
SELECT COUNT(*) FROM TestTab
SELECT COUNT(*) FROM TestTab2
GO
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 showfilestats
GO