SQL Server 删除文件/文件组问题

SQL Server 删除文件/文件组问题

在做表分区或其他情形时，经常会遇到移除历史文件、文件组报错的问题，本文将通过实验的方式，发现什么情形会出现删除文件、文件组报错，报错的内容，最终总结删除文件文件组的方式及脚本。

一、在文件组上创建空表后删除文件/文件组测试

直接上测试脚本

USE [master];
GO
ALTER DATABASE [test] ADD FILEGROUP RemoveFileGroup;
ALTER DATABASE [test] ADD FILE ( 
    NAME = N'RemoveFile'
   , FILENAME = N'D:\DB\RemoveFile.ndf' 
   , SIZE = 1024KB 
   , FILEGROWTH = 1024KB )
TO FILEGROUP [RemoveFileGroup];
GO
USE test;
GO
CREATE TABLE RemoveFileTest(id INT) ON RemoveFileGroup;
USE [master];
GO
ALTER DATABASE test remove FILE RemoveFile;

在文件组创建空表，可以删除文件。当我们使用如下脚本删除文件组时

ALTER DATABASE test remove filegroup RemoveFileGroup;

会报文件组不为空的错误，无法删除文件组，如下所示

文件删除后，再向RemoveFileTest表中插入一行数据

INSERT INTO RemoveFileTest VALUES(1);

结果无法插入，报如下文件组未分配文件错误

二、表中插入数据后删除文件/文件组测试

备份数据库日志，在文件组RemoveFileGroup上重建RemoveFile 文件，并向表RemoveFileTest表中插入一条数据

BACKUP LOG [test] TO  DISK = N'E:\backup\test_log_20190214.bak'
WITH NOFORMAT, NOINIT,  NAME = N'test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
USE [master];
GO
ALTER DATABASE [test] ADD FILE (
       NAME = N'RemoveFile'
       , FILENAME = N'D:\DB\RemoveFile.ndf'
       , SIZE = 1024KB
       , FILEGROWTH = 1024KB )
TO FILEGROUP [RemoveFileGroup];
INSERT INTO RemoveFileTest VALUES(1);

执行如下删除文件脚本

ALTER DATABASE test remove FILE RemoveFile;

报5042文件非空错误，错误信息如下：

因为RemoveFileGroup文件组下仅有一个文件RemoveFile，所以此时我们用如下脚本，将RemoveFile中的内容移至同文件组的其他文件会报错：

USE [test]
GO
DBCC SHRINKFILE (N'RemoveFile' ,EMPTYFILE)
GO

DBCC SHRINKFILE: 无法移动堆页 31:8。

消息 2555，级别 16，状态 1，第 55 行

无法将文件 "RemoveFile" 的所有内容移到其他位置，以完成清空文件操作。

语句已终止。

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

消息 1105，级别 17，状态 2，第 55 行

无法为数据库 'test' 中的对象 'dbo.RemoveFileTest' 分配空间，因为 'RemoveFileGroup' 文件组已满。请删除不需要的文件、删除文件组中的对象、将其他文件添加到文件组或为文件组中的现有文件启用自动增长，以便增加可用磁盘空间。

执行删除文件组脚本，同样报5042，文件组非空错误。

三、清空数据表，再执行删除文件/文件组测试

DELETE 清空表测试

--1.DELETE 清空表
DELETE RemoveFileTest;
ALTER DATABASE test remove FILE RemoveFile;

发现，使用DELETE清空表后，删除文件，仍然报文件不为空错误，如下：

数据已经清空了，按理应该文件也空了，但却报文件非空错误，那么文件中还剩余什么内容呢？为弄清问题所在，我们再执行收缩文件，将文件清空，文件内容移入同文件组的其他文件，执行结果如下：

同样，执行如下脚本，并查看结果：

DBCC TRACEON(3604,2588)
DBCC IND(test,'RemoveFileTest',-1)

可以看到PageFID=33，即RemoveFile文件上存在着一页PageType=10的页，即IAM页。删除IAM页的方法

• TRUNCATE TABLE

执行如下脚本

TRUNCATE TABLE RemoveFileTest;

再执行查看文件页的脚本

DBCC TRACEON(3604,2588)
DBCC IND(test,'RemoveFileTest',-1)

发现此时不再有数据，执行删除文件脚本

ALTER DATABASE test remove FILE RemoveFile;

文件顺利删除。

TRUNCATE TABLE 清空表测试

--2. TRUNCATE TABLE 清空数据
BACKUP LOG [test] TO  DISK = N'E:\backup\test_log_20190214.bak'
WITH NOFORMAT, NOINIT,  NAME = N'test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
USE [master];
GO
ALTER DATABASE [test] ADD FILE (
       NAME = N'RemoveFile'
       , FILENAME = N'D:\DB\RemoveFile.ndf'
       , SIZE = 1024KB
       , FILEGROWTH = 1024KB )
TO FILEGROUP [RemoveFileGroup];
INSERT INTO RemoveFileTest VALUES(1);
TRUNCATE TABLE RemoveFileTest;

执行删除文件脚本，文件可以顺利删除。

 

综上所述，要清空需要删除文件上的表，使用TRUNCATE 真正清空所有页，而DELETE只能删除数据，而不能删除已经分配给索引、IAM等页面。

直接删除需要删除文件上的表

--3.直接删除需要删除文件上的表
DROP TABLE RemoveFileTest;

执行删除文件脚本，文件可以顺利删除。

 

四、文件组上创建分区方案删除文件/文件组测试

 

1. 每个分区单独文件/文件组，可以参照分区合并

2. 多个分区在一个文件组，可能需要删除分区函数及对应的分区方案（前提是删除了分区表，或将分区表转化为普通表）

 

五、删除文件/文件组准备

确定删除文件对应文件组中有哪些表、索引、分区方案

--查看文件组上的表、索引、分区方案
select OBJECT_NAME(object_id) TableName,i.name IndexName,ds.type_desc from sys.indexes i
left join sys.data_spaces ds on i.data_space_id=ds.data_space_id
where ds.name='userIP_log_20171201'   --文件组名称
--查看文件组上的分区方案
select distinct ds.name fileGroupName
	,ps.name partitionSchemaName
	,pf.name partitionFunctionName
from sys.data_spaces ds
left join sys.destination_data_spaces dds on ds.data_space_id=dds.data_space_id
left join sys.partition_schemes ps on ps.data_space_id=dds.partition_scheme_id
left join sys.partition_functions pf on ps.function_id=pf.function_id
where ds.name='RangeSchema_CreateTime'      --文件组名称

确定是否保留表数据、结构、索引、分区方案

需要保留的，转移到其他文件/文件组。同文件组不同文件之间内容转移可以使用DBCC SHRINKFILE的EMPTYFILE参数，将指定文件中的内容转移到同文件组中的其他文件中。

DBCC SHRINKFILE (N'RemoveFile' ,EMPTYFILE)

不同文件组中的内容转移可以通过在转移的目标文件组上创建聚集索引、重建非聚集索引等方式处理

不需保留的，TRUNCATE或者DROP掉

 

最后删除文件/文件组

注意，删除文件组之前，要先删除文件组中所有文件。

如果喜欢，可以关注 MSSQLServer 公众号，将有更多精彩内容分享。