删除SQL数据库文件中多余的主文件.mdf,以及文件组

由于数据库文件多了,得重新规划,所以必须得删除多余的文件和文件组。本文简单测试,最终只保留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

————————————————
版权声明:本文为CSDN博主「薛定谔的DBA」的原创文章。
原文链接:https://blog.csdn.net/kk185800961/article/details/52120505

文件和文件组。本文简单测试,最终只保留primary文件组和一个主文件。

  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值