亂馬客

最近客戶的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的使用量，如下，

或是透過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

 

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

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

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

 

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

 

而決定什麼時候將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，

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

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

 

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:從零開始的軟體開發生活」

請大家繼續支持 ^_^