SQLServer 删除文件及文件组

概述由于数据库文件多了，得重新规划，所以必须得删除多余的文件和文件组。本文简单测试，最终只保留primary文件组和一个主文件。 --模拟测试USE masterGO-- DROP DATABASE [TestDB]CREATE DATABASE [TestDB] ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\MSSQLDateFiles\T

由于数据库文件多了，得重新规划，所以必须得删除多余的文件和文件组。本文简单测试，最终只保留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',fileGROUP [FG1] ( name = N'file1',filename = N'D:\MSsqlDatefiles\TestDB\file1.ndf',( name = N'file2',filename = N'D:\MSsqlDatefiles\TestDB\file2.ndf',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,[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,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,line 1The file 'file1' cannot be removed because it is not empty.Msg 5042,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],'TestTab2')  GO  DBCC showfilestatsGO

总结

以上是内存溢出为你收集整理的SQLServer 删除文件及文件组全部内容，希望文章能够帮你解决SQLServer 删除文件及文件组所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。