2007~2011/SQL Server2009. 10. 17. 00:01

SQL Server 에서 ndf 파일을 추가하였으나 더이상 필요하지 않아 이를 삭제하려고 합니다. 물론 ndf 데이터 파일에 저장된 데이터는 mdf 파일로 마이그레이션(병합) 해야 합니다. 어떤 방법이 있을까요? SHRINKFILE 을 이용할 수 있습니다. 

다음 시나리오는 NDF 파일을 추가하고 데이터를 INSERT 한 후, 다시 NDF 파일을 삭제하며 데이터가 MDF 로 옮겨지는 과정을 진행한 스크립트입니다.

CREATE DATABASE LAIGO

-- ndf 데이터 파일을 추가합니다.
ALTER DATABASE LAIGO
ADD FILE
( NAME = LAIGO_DATA1, FILENAME = 'D:\DATA\LAIGO_DATA1.ndf', SIZE = 3MB, FILEGROWTH = 1MB)


-- 데이터를 추가합니다.
USE LAIGO
GO

CREATE TABLE TBL_DATA
(
 A INT,
 B CHAR(10),
 C CHAR(100)
)

DECLARE @count INT
SET @count = 1

WHILE @count < 50000
BEGIN
 INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('A',100))
 SET @count = @count + 1
 INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('B',100))
 SET @count = @count + 1
 INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('C',100))
 SET @count = @count + 1
END


-- mdf, ndf 파일의 size 가 늘어났음을 확인할 수 있습니다.
SP_HELPDB 'LAIGO'
/*
name          db_size
----------------------
LAIGO         8.81 MB 
 
name         fileid   filename                    filegroup                      size           maxsize        growth        usage
---------------------------------------------------------------------------------------------------------
LAIGO            1     D:\DATA\LAIGO.mdf            PRIMARY     4352 KB            Unlimited    1024 KB    data only
LAIGO_log      2     D:\DATA\LAIGO_log.LDF      NULL             576 KB     2147483648 KB        10%     log only
LAIGO_DATA1 3     D:\DATA\LAIGO_DATA1.ndf  PRIMARY     4096 KB            Unlimited    1024 KB    data only


SELECT * FROM TBL_DATA11
-- (50001 row(s) affected)


-- SHRINKFILE 'EMPTYFILE' 옵션을 사용하여 모든 데이터를 동일한 파일 그룹의 다른 파일로 마이그레이션 합니다.
DBCC SHRINKFILE('LAIGO_DATA1',EMPTYFILE)
/*
DbId   FileId      CurrentSize    MinimumSize    UsedPages   EstimatedPages
---------------------------------------------------------------------------
11     3             512                 384                  0                0

(1 row(s) affected)
*/

-- 추가한 ndf 파일을 제거합니다.
ALTER database LAIGO REMOVE FILE LAIGO_DATA1
/* 파일 'LAIGO_DATA1'이(가) 제거되었습니다. */


-- 기존 ndf 에 저장된 데이터가 mdf 로 마이그레이션 되었음을 확인할 수 있습니다.
SELECT * FROM TBL_DATA11
/* (50001 row(s) affected) */


SP_HELPDB 'LAIGO'
/*
name          db_size
----------------------
LAIGO         9.06 MB

name         fileid   filename                    filegroup                      size           maxsize        growth        usage
---------------------------------------------------------------------------------------------------------
LAIGO            1     D:\DATA\LAIGO.mdf            PRIMARY     8448 KB            Unlimited    1024 KB    data only
LAIGO_log      2     D:\DATA\LAIGO_log.LDF      NULL             832 KB     2147483648 KB        10%     log only
*/



작성자 : Lai Go / 작성일자 : 2009.10.14
Posted by Lai Go