之前写过一篇文章:SQLSERVER将一个文件组的数据移动到另一个文件组
每个物理文件(数据文件)对应一个文件组的情况(一对一)
如果我把数据移到另一个文件组了,不想要这个已经清空的文件组了,怎麽做?
脚本跟之前那篇文章差不多
1 USE master 2 GO 3 4 5 IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test')) 6 DROP DATABASE [Test] 7 8 --1.创建数据库 9 CREATE DATABASE [Test]10 GO11 12 USE [Test]13 GO14 15 16 --2.创建文件组17 ALTER DATABASE [Test]18 ADD FILEGROUP [FG_Test_Id_01]19 20 ALTER DATABASE [Test]21 ADD FILEGROUP [FG_Test_Id_02]22 23 24 25 --3.创建文件26 ALTER DATABASE [Test]27 ADD FILE28 (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:/FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )29 TO FILEGROUP [FG_Test_Id_01];30 31 ALTER DATABASE [Test]32 ADD FILE33 (NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:/FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )34 TO FILEGROUP [FG_Test_Id_02];35 36 37 --4.创建表,这个表的数据存放在[FG_Test_Id_01] 文件组上38 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 39 GO40 41 42 --5.插入数据43 INSERT INTO [dbo].[aa]44 SELECT 1,REPLICATE('s',3000)45 GO 50046 47 48 --6.查询数据49 SELECT * FROM [dbo].[aa]50 51 52 --7.创建聚集索引在[FG_Test_Id_02]文件组上53 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02]54 GO55 56 57 --8.我们查看一下文件组的逻辑文件名58 EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname59 65 66 --9.移除FG_Test_Id_01文件组67 ALTER DATABASE TEST68 REMOVE FILE FG_TestUnique_Id_01_data
当你移动数据到文件组[FG_Test_Id_02]上时,这时候文件组[FG_Test_Id_01]里面已经没有数据了使用下面的脚本查看
1 --数据库文件、大小和已经使用空间 2 USE [Test] --要查看的当前数据库的使用空间,自动增长大小,数据库文件位置 3 GO 4 set nocount on 5 create table #Data( 6 FileID int NOT NULL, 7 [FileGroupId] int NOT NULL, 8 TotalExtents int NOT NULL, 9 UsedExtents int NOT NULL,10 [FileName] sysname NOT NULL,11 [FilePath] nvarchar(MAX) NOT NULL,12 [FileGroup] varchar(MAX) NULL)13 14 create table #Results(15 db sysname NULL ,16 FileType varchar(4) NOT NULL,17 [FileGroup] sysname not null,18 [FileName] sysname NOT NULL,19 TotalMB numeric(18,2) NOT NULL,20 UsedMB numeric(18,2) NOT NULL,21 PctUsed numeric(18,2) NULL,22 FilePath nvarchar(MAX) NULL,23 FileID int null)24 25 create table #Log(26 db sysname NOT NULL,27 LogSize numeric(18,5) NOT NULL,28 LogUsed numeric(18,5) NOT NULL,29 Status int NOT NULL,30 [FilePath] nvarchar(MAX) NULL)31 32 INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath])33 EXEC ('DBCC showfilestats WITH NO_INFOMSGS')34 35 update #Data36 set #Data.FileGroup = sysfilegroups.groupname37 from #Data, sysfilegroups38 where #Data.FileGroupId = sysfilegroups.groupid39 40 INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID)41 SELECT DB_NAME() db,42 [FileGroup],43 'Data' FileType,44 [FileName],45 TotalExtents * 64./1024. TotalMB,46 UsedExtents *64./1024 UsedMB,47 UsedExtents*100. /TotalExtents UsedPct,48 [FilePath],49 FileID50 FROM #Data51 order BY --1,252 DB_NAME(), [FileGroup]53 54 insert #Log (db,LogSize,LogUsed,Status)55 exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ')56 57 insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID)58 select DB_NAME() db,59 'Log' [FileGroup],60 'Log' FileType,61 s.[name] [FileName],62 s.Size/128. as LogSize ,63 FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,64 ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct,65 s.FileName FilePath,66 s.FileID FileID67 from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s68 where f.dbid = DB_ID()69 and (s.status & 0x40) <> 070 and s.FileID = f.FileID71 and l.db = DB_NAME()72 73 SELECT r.db AS "Database",74 r.FileType AS "File type",75 CASE76 WHEN r.FileGroup = 'Log' Then 'N/A'77 ELSE r.FileGroup78 END "File group",79 r.FileName AS "Logical file name",80 r.TotalMB AS "Total size (MB)",81 r.UsedMB AS "Used (MB)",82 r.PctUsed AS "Used (%)",83 r.FilePath AS "File name",84 r.FileID AS "File ID",85 CASE WHEN s.maxsize = -1 THEN null86 ELSE CONVERT(decimal(18,2), s.maxsize /128.)87 END "Max. size (MB)",88 CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"89 FROM #Results r90 INNER JOIN dbo.sysfiles s91 ON r.FileID = s.FileID92 ORDER BY 1,2,3,4,593 94 DROP TABLE #Data95 DROP TABLE #Results96 DROP TABLE #LogView Code
使用下面的SQL语句移除文件组[FG_Test_Id_01]就可以了
5 --9.移除FG_Test_Id_01文件组6 ALTER DATABASE TEST7 REMOVE FILE FG_TestUnique_Id_01_data
此时就只剩下主文件组和[FG_Test_Id_02]文件组了
注意:如果不使用聚集索引来移动文件组[FG_Test_Id_01]上的数据到文件组[FG_Test_Id_02]
1 --4.创建表,这个表的数据存放在[FG_Test_Id_01] 文件组上2 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 3 GO
直接使用下面SQL语句来收缩文件会报错
1 -收缩一下FG_Test_Id_01文件组文件2 DBCC SHRINKFILE(FG_TestUnique_Id_01_data,EMPTYFILE)
报错内容
1 DBCC SHRINKFILE: 无法移动堆页 3:515。2 消息 2555,级别 16,状态 1,第 1 行3 无法将文件 "FG_TestUnique_Id_01_data" 的所有内容移到其他位置,以完成清空文件操作。4 语句已终止。5 DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。6 消息 1105,级别 17,状态 2,第 1 行7 无法为数据库 'Test' 中的对象 'dbo.aa' 分配空间,因为 'FG_Test_Id_01' 文件组已满。请删除不需要的文件、删除文件组中的对象、将其他文件添加到文件组或为文件组中的现有文件启用自动增长,以便增加可用磁盘空间。
因为文件组[FG_Test_Id_01]里还有数据,不能清空
两个物理文件(数据文件)对应一个文件组的情况(一对多)
上面的情况是每个物理文件(数据文件)对应一个文件组的情况
下面这种情况是两个物理文件(数据文件)对于一个文件组的情况
一对一的情况使用聚集索引里移动数据,而一对一的情况使用DBCC SHRINKFILE
创建数据库
test1和test2这两个数据文件归属于主文件组primary,而数据文件test1最大大小为6MB初始大小为5MB
test2数据文件最大大小没有限制
使用下面脚本添加数据到主文件组
1 --1.创建表,这个表的数据存放在主文件组上 2 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) 3 GO 4 5 6 --2.插入数据 7 INSERT INTO [dbo].[aa] 8 SELECT 1,REPLICATE('s',3000) 9 GO 60010 11 12 --3.查询数据13 SELECT * FROM [dbo].[aa]14 15 16 17 18 --4.我们查看一下文件组的逻辑文件名19 EXEC [sys].[sp_helpdb] @dbname = TEST120 -- sysname21 SELECT DB_NAME(database_id) AS DatabaseName ,22 Name AS Logical_Name ,23 Physical_Name ,24 ( size * 8 ) / 1024 SizeMB25 FROM sys.master_files26 WHERE DB_NAME(database_id) = 'Test1'
因为第一个数据文件的最大大小限制,所以有一部分数据插入到了test2.ndf
现在修改test1数据文件的最大大小限制为20MB
相关SQL
1 ALTER DATABASE [Test1] MODIFY FILE(name='Test1',SIZE=5MB, filegrowth=1MB, MAXSIZE=20MB)View Code
执行下面的SQL语句
1 --5.收缩文件2 DBCC SHRINKFILE(test2,EMPTYFILE)3 4 5 --6.移除test2数据文件test2.ndf6 ALTER DATABASE TEST17 REMOVE FILE test2
在执行第五条语句的时候,执行下面脚本
新闻热点
疑难解答