/**********************************************************************************************
-- Title : [2k5] 데이터베이스 파일 및 파일그룹 축소 및 제거(emptyfile)
-- Reference : hanbitbook.co.kr
-- Key word : shirink database file emptyfile 파일그룹 제거
**********************************************************************************************/
IF EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE name = 'shrinkDB')
DROP DATABASE shrinkDB;
CREATE DATABASE shrinkDB
ON PRIMARY
( NAME = shrinkDB,
FILENAME = N'd:\shrinkDB.mdf' ,
SIZE = 3MB
)
LOG ON
( NAME = shrinkDB_log,
FILENAME = N'd:\shrinkDB_log.LDF' ,
SIZE = 1MB
);
GO
USE shrinkDB;
CREATE TABLE test
(txt VARCHAR(1024)
);
GO
DECLARE @i INT;
SET @i = 0;
WHILE @i < 1024 * 10 -- 1024바이트*1024*10 = 10Mbyte
BEGIN
INSERT INTO test VALUES(REPLICATE( 'A' , 1024 ));
SET @i = @i + 1;
END;
DECLARE @i INT;
SET @i = 0;
WHILE @i < 1024 * 10 -- 1024*1024*10 = 10Mbyte
BEGIN
INSERT INTO test VALUES(REPLICATE( 'B' , 1024 ));
SET @i = @i + 1;
END;
DECLARE @i INT;
SET @i = 0;
WHILE @i < 1024 * 10 -- 1024*1024*10 = 10Mbyte
BEGIN
INSERT INTO test VALUES(REPLICATE( 'C' , 1024 ));
SET @i = @i + 1;
END;
GO
DELETE test
WHERE SUBSTRING(txt, 1, 1) = 'B'; -- 행 데이터의 첫 글자가'B'인 행
-- 데이터베이스 줄이기
DBCC SHRINKDATABASE (shrinkDB);
GO
DELETE test
WHERE SUBSTRING(txt, 1, 1) = 'A'; -- 행 데이터의 첫 글자가'B'인 행
DBCC SHRINKDATABASE (shrinkDB, TRUNCATEONLY);
GO
DBCC SHRINKDATABASE (shrinkDB, 10);
GO
USE shrinkDB;
DECLARE @i INT;
SET @i = 0;
WHILE @i < 1024 * 10 -- 1024*1024*10 = 10Mbyte
BEGIN
INSERT INTO test VALUES(REPLICATE( 'D' , 1024 ));
SET @i = @i + 1;
END;
DELETE test WHERE SUBSTRING(txt, 1, 1) = 'C';
GO
DBCC SHRINKFILE(shrinkDB_log);
-- 데이터 파일 제거
ALTER DATABASE shrinkDB
ADD FILE
( NAME = shrinkDB2,
FILENAME = N'd:\shrinkDB2.ndf' ),
( NAME = shrinkDB3,
FILENAME = N'd:\shrinkDB3.ndf' )
TO FILEGROUP [PRIMARY];
USE shrinkDB;
DECLARE @i INT;
SET @i = 0;
WHILE @i < 1024 * 30 -- 1024*1024*30 = 30Mbyte
BEGIN
INSERT INTO test VALUES(REPLICATE( 'E' , 1024 ));
SET @i = @i + 1;
END;
ALTER DATABASE shrinkDB --파일 'shrinkDB3'은(는) 비어 있지 않으므로 제거할 수 없습니다.(주파일은 안되는듯)
REMOVE FILE shrinkDB3;
DBCC SHRINKFILE (shrinkDB2, EMPTYFILE);
ALTER DATABASE shrinkDB
REMOVE FILE shrinkDB2;