[SQL]透過DBCC SHRINKFILE來讓同個File Group的檔案使用量差不多

本篇文章介紹透過 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將慢慢地將資料移轉到其他相同 File Group 的檔案之中,並讓這些檔案使用量差不多。

最近客戶的SQL要移機,在原本的DB中只有一個Data File,而新機想要建立多個Data File放在不同的Disk上面!

目前想到的方式有3個,如下,

1.在新機上先建立Database,並建立多個Data File,然後再從舊DB將資料Export過去。

2.在新機上先建立Database,並建立多個Data File,並對應多個File Group,然後將資料比較多資料表的Index建立到那些File Group。

類似如下(感謝百敬老師的指導),

ALTER TABLE tbl ADD CONSTRAINT [PK_testmember] PRIMARY KEY CLUSTERED 
(
MEMBERID ASC
)
WITH (ONLINE=ON)
ON targetFG

 

3.將舊機的備份檔還原回來,再建立其他的Data File,再透過DBCC SHRINKFILE來讓新增的檔案使用量差不多。

關於 DBCC SHRINKFILE 的用法,可參考「透過 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將資料移到另一個資料檔之中」。

因為 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 是一直將慢慢地將資料移轉到其他相同File Group 的檔案之中,最後可以透過Alter Database將該檔案移除。

但我們要的是讓這些資料檔使用量差不多,所以除了給它沖下去外,隨時看看各資料量是不是差不多了,如果差不多就要停止EmptyFile,以下一步步的來介紹,

3.1.還原回來後,可透過SSMS中「Disk Usage」標準報表來看一下目前mdf的使用量,如下,

image

或是透過SQL來查詢

USE [使用的DB]
GO

SELECT df.name 
, FILEPROPERTY(df.name, 'SpaceUsed') AS dataPages--回傳資料的頁數,每頁8K 
-- 所以要取出資料的MB就要 * 8 / 1024 => / 128
, CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(df.name,'SpaceUsed')/128.000,2)) AS dataMBs 
, *
FROM sys.database_files df  
WHERE
--只針對DataFile
  df.type = 0;

 

3.2.建立其他的Data File

image

 

3.3.執行 DBCC SHRINKFILE([要清空的File], EMPTYFILE)

USE [使用的DB]
GO
DBCC SHRINKFILE([要清空的mdf File], EMPTYFILE)

因為它會慢慢地將資料移轉到其他相同File Group 的檔案之中,所以就給它慢慢的執行。

image

 

3.4.新增一個執行視窗,執行SQL,當資料差不多時,就Kill Step 3 的 Session (假設Session Id為77)

image

 

而決定什麼時候將Session Kill掉,我的方式是取得各Data File的使用量除以某個數值(可能是10MB or 100MB) 後,再比較這些檔案是否相同,或是mdf的使用量小於其他的Data File。

而如何判斷相同或是小於其他使用量,可以使用 DENSE_RANK ,所以整個的SQL如下,

USE [使用的DB]
GO

--如果3個檔案Size差不多,就停止 DBCC SHRINKFILE([要清空的mdf File], EMPTYFILE)
DECLARE @SimilarMB INT
DECLARE @FileCount INT 
-- (100M以內)
SET @SimilarMB = 100;
--取得 FileCount 的數量
SELECT @FileCount = COUNT(*) 
FROM sys.database_files 
WHERE type = 0;

WHILE (
		--Size類似
		(@FileCount <> (SELECT SUM(Seq) FROM
						(SELECT DENSE_RANK() OVER ( ORDER BY FILEPROPERTY(df.name,'SpaceUsed') /128 / @SimilarMB ) AS Seq
							FROM sys.database_files df 
							--只針對DataFile
							WHERE df.type = 0
						) t1
					)
		)
		--並且 mdf 使用量 已經比其他的file 還大
		AND 
		(
			(SELECT Seq FROM
						(SELECT DENSE_RANK() OVER ( ORDER BY FILEPROPERTY(df.name,'SpaceUsed') /128 / @SimilarMB ) AS Seq 
						,df.file_id
							FROM sys.database_files df 
							--只針對DataFile
							WHERE df.type = 0
						) t1
						WHERE t1.file_id = 1
			) > 1
		)
)
BEGIN
	PRINT 'wait empty file';
	--每3秒執行一次
	WAITFOR DELAY '00:00:03';
END 
 
--設定檔案差不多大小時,要取消的Session Id
KILL 77;
--最後將最後的結果Select出來
SELECT df.name 
	, FILEPROPERTY(df.name, 'SpaceUsed') AS dataPages--回傳資料的頁數,每頁8K 
	-- 所以要取出資料的MB就要 * 8 / 1024 => / 128
	, CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(df.name,'SpaceUsed')/128.000,2)) AS dataMBs 
	, DENSE_RANK() OVER ( ORDER BY FILEPROPERTY(df.name,'SpaceUsed') /128 / @SimilarMB ) AS Seq
	FROM sys.database_files df  
	--只針對DataFile
	WHERE df.type = 0;

 

最後可看到每個Data File約 3.3GB,

image

也可以從「Disk Usage」標準報表來看,

image

而原本在執行 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 就被強迫停止了,如下,

image

 

DBCC SHRINKFILE 會慢慢地將資料移轉到其他相同File Group 的檔案之中,所以可以隨時停止它,而且不會已經搬過去的資料不會被Rollback哦!

使用 DBCC SHRINKFILE 沒辦法將 mdf 中所有的資訊移到別的 ndf 去,如Database Catalogs。

做過 DBCC SHRINKFILE 可能會造成 high fragment, 所以建議之後再做 Rebuild Index。

參考資料

MSSQL: Spilt SQL Server Datafile to multiple files

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^