lob 빈 공백으로 업데이트 후 공간 확보하기
lob compaction 이 힘들때 work-around 입니다.
/* 샘플 데이터베이스 생성 */
CREATE DATABASE lob ON PRIMARY
( NAME = N'lob', FILENAME = N'f:\MSSQL\lob.MDF' , SIZE = 225280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N'lob_LOG', FILENAME = N'f:\MSSQL\lob_LOG.LDF' , SIZE = 225280KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
USE lob
GO
IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
CREATE TABLE TBLX
(IDX INT IDENTITY(1,1)
,COLA TEXT
)
GO
SET NOCOUNT ON
DECLARE @IDX INT
SET @IDX = 0
DECLARE @LOB_DATA VARCHAR(MAX)
SET @LOB_DATA = REPLICATE(cast('A'as varchar(max)), 20000)
WHILE (1=1)
BEGIN
INSERT INTO TBLX (COLA) VALUES(case when @idx % 2 = 1 then @LOB_DATA else left(@lob_data,10) end)
SET @IDX = @IDX + 1
IF @IDX > 3000 BREAK
END
GO
SELECT TOP 100 DATALENGTH(CAST(COLA AS VARCHAR(MAX))) FROM TBLX
GO
CREATE UNIQUE CLUSTERED INDEX CL_TBLX ON TBLX (IDX)
GO
/* 샘플 데이터베이스 생성 완료 */
/* lob 를 공백으로 업데이트 하기전 공간 사용량 */
DBCC EXTENTINFO ('lob','TBLX')
go
-- 490개 행에 pfs_bytes 가 꽉 차 있음
/* 일정기간보다 오래된 text 컬럼을 공백으로 update 하는 시뮬레이션 */
update tblx set COLA ='' where idx > 1000
go
/* lob 를 공백으로 업데이트 한 후 공간 사용량 */
DBCC EXTENTINFO ('lob','TBLX')
-- 220개 행이 출력되며, pfs_bytes 의 중간 중간 비어있는것을 알수 있음 (알박기 되어 있어 extents 를 날릴수 없음)
/* sp_spaceused 를 이용한 공간 사용량 확인 */
exec sp_spaceused tblx
go
-- unused 공간이 2MB 있음
/* text 공간이 삭제된 row 를 다른 빈 테이블로 옮기고 삭제한 후 다시 원복시킴 */
if object_id ('tbly') is not null
drop table tbly
go
select *
into tbly
from (
select idx from tblx where idx > 1000 -- lob 는 select list 에서 빼야 한다.
) a
go
delete from tblx where idx > 1000
go
set identity_insert tblx on
go
insert into tblx (idx)
select idx from tbly
go
/* 작업완료 된 후 pfs_bytes 현황 */
DBCC EXTENTINFO ('lob','TBLX')
go
-- 174개의 extenst 가 사용되었으며, 중간 중간 있던 알박기 페이지는 모두 삭제 되었음
/* sp_spaceused 를 이용한 공간 사용량 확인 */
exec sp_spaceused tblx
go
-- 완전히 공간이 확보된 모습