[SQL]透過 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將資料移到另一個資料檔之中

2013-12-25

13234
0
SQL
2014-08-29

要如何要資料庫其中一個檔案中的資料移到另一個資料檔，以方便將該資料檔刪除呢?
我們可以透過 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將資料移到另一個資料檔之中。
而DBCC SHRINKFILE([要清空的File], EMPTYFILE) 又有何限制呢?
以下將實際來實兵演練整個過程。

最近Kastusa大大問說，他們有一個資料庫，有因為次磁碟滿了，所以再建立一個新的資料檔。

每次將資料還原到測試機時，都要指定 2 個資料檔的位置，不太方便。

是否有什麼方式可以將資料移到另一個資料檔，然後將它另一個資料檔刪除呢?

馬上就想到強哥在Tech Days上說的「DBA 十大噩夢-空中換引擎解救即將爆滿的硬碟」，透過DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將資料移到另一個資料檔之中。

以下就來演練一下吧!

1.建立測試的DB

USE master
go
DROP DATABASE TestDB
go

CREATE DATABASE TestDB;

--先做一次full backup
BACKUP DATABASE TestDB
TO DISK = 'nul'

2.看一下TestDB的實體檔案在那裡

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'TestDB'
/*
D:\MSSQL\Data\TestDB.mdf
D:\MSSQL\Log\TestDB_log.ldf
*/

3.假設資料成長到讓磁碟滿了，這時有2個方式可以處理

3.1.在另個磁碟再建一個 ndf

ALTER DATABASE TestDB
ADD FILE 
(
    NAME = TestDB2,
    FILENAME = 'e:\TestDB2.ndf',
    SIZE = 5MB,
    FILEGROWTH = 1MB
);

3.2.將資料檔搬到另一個磁碟去 (請別執行，以下演練使用 3.1 的方式)

--http://blog.sqlauthority.com/2012/10/28/sql-server-move-database-files-mdf-and-ldf-to-another-location/
USE master;
GO
-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC master.dbo.sp_detach_db @dbname = N'TestDB'
GO

--Copy File
EXEC sys.xp_cmdshell 'move D:\MSSQL\Data\TestDB.mdf E:\TestDB.mdf'
EXEC sys.xp_cmdshell 'move D:\MSSQL\Log\TestDB_log.ldf E:\TestDB_log.ldf'

-- Attach DB
CREATE DATABASE [TestDB] ON
( FILENAME = N'E:\TestDB.mdf' ),
( FILENAME = N'E:\TestDB_log.ldf' )
FOR ATTACH
GO

而Kastusa大大目前的狀況是新增另一個資料檔在其它磁碟，要將其中一個資料檔清空然後刪除。

在清空之前，先來灌資料進去吧!

--切到TestDB
USE TestDB
go

--建立測試用資料表
CREATE TABLE SALES
    (
      CUSTOMER_ID INT NOT NULL ,
      ITEM_ID INT NOT NULL ,
      SALE_QUANTITY SMALLINT NOT NULL ,
      SALE_DATE DATE NOT NULL
    );
go

--先看一下目前資料的大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'TestDB'

--將資料加進去
--http://www.bennadel.com/blog/1473-Seeding-SQL-RAND-Method-With-NEWID-For-Per-Row-Random-Values-Thanks-Joshua-Cyr-.htm
INSERT INTO SALES
SELECT RAND(CAST( NEWID() AS varbinary )) * 900 + 1 AS CUSTOMER_ID
    , RAND(CAST( NEWID() AS varbinary )) * 500 + 1 AS ITEM_ID
	, RAND(CAST( NEWID() AS varbinary )) * 10 + 1 AS SALE_QUANTITY
	, DATEADD(D, RAND(CAST( NEWID() AS varbinary )) * 100, '2000/1/1') AS  SALE_DATE
FROM sys.columns
go 5000

--資料新增完，再來看看資料檔中的資料大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'TestDB'

因為原本的mdf檔是滿的，所以就把mdf清空看看，如下，

--清mdf檔來將資料移到另一個資料檔之中
DBCC SHRINKFILE(TestDB, EMPTYFILE)

結果發生2555的錯誤，如下，

Msg 2555, Level 16, State 1, Line 98
無法將檔案 "TestDB" 的所有內容移動到其他位置以完成 emptyfile 作業。
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。

因為沒辦法把 mdf 檔給清空(mdf包含Database Catalogs，詳細請參考「http://blog.sqlauthority.com/2010/06/15/sql-server-shrinking-ndf-and-mdf-files-readers%E2%80%99%C2%A0opinion/」」，所以只能先搬資料檔位置，再把 ndf 的資料移到 mdf 去，如下，

4.將資料檔搬到另一個磁碟去

USE master;
GO
--先看一下TestDB的Path在那裡
SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'TestDB'

-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC master.dbo.sp_detach_db @dbname = N'TestDB'
GO

--Copy File
EXEC sys.xp_cmdshell 'move D:\MSSQL\Data\TestDB.mdf E:\TestDB.mdf'
EXEC sys.xp_cmdshell 'move D:\MSSQL\Log\TestDB_log.ldf E:\TestDB_log.ldf'
--TestDB2.ndf原本就在E:所以不用移

--Re Create File 
CREATE DATABASE [TestDB] ON
( FILENAME = N'E:\TestDB.mdf' ),
(FILENAME = N'e:\TestDB2.ndf'),
( FILENAME = N'E:\TestDB_log.ldf' )
FOR ATTACH
GO

--最後再看一下TestDB的Path是否有搬成功
SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE DB_NAME(database_id)  = 'TestDB'

5.再將 ndf 檔案清空

USE TestDB;
GO
--清 ndf 檔來將資料移到另一個資料檔之中
DBCC SHRINKFILE(TestDB2, EMPTYFILE)

6.再來就可以將 ndf 檔案刪除掉

--清完後，就可以將檔案移除了
ALTER DATABASE TestDB
REMOVE FILE TestDB2

所以這樣備份後還原就只有一個mdf檔了。

結論

透過以上的演練，可以學習到

1.透過 DBCC SHRINKFILE([要清空的File], EMPTYFILE) 來將資料移到另一個資料檔之中 (mdf檔無法整個清空)。

2.透過 RAND(CAST( NEWID() AS varbinary )) 來產生亂數值。

3.透過 sp_detach_db ,sys.xp_cmdshell, CREATE DATABASE 來搬移資料檔。

希望對大家有所幫助，謝謝!

Hi,

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

請大家繼續支持 ^_^

回首頁

亂馬客

https://rainmakerho.github.io/