대량 처리와 배치 삭제

SELECT num, board_num, user_num, title, hit_count, reg_date
INTO Board.T_PURGE FROM Board.POSTS_BIG;

ALTER TABLE Board.T_PURGE ADD CONSTRAINT PK_T_PURGE PRIMARY KEY CLUSTERED (num);
CREATE INDEX IX_PURGE_REG ON Board.T_PURGE (reg_date);
GO

SELECT COUNT(*) FROM Board.T_PURGE;                                   -- 500,000
SELECT COUNT(*) FROM Board.T_PURGE WHERE reg_date < '2026-07-01';   -- 260,639

CHECKPOINT;   -- 로그 사용량을 재기 좋게 정리해 둡니다
GO

DECLARE @b decimal(18,2), @a decimal(18,2), @t datetime2(7), @r int;
SELECT @b = used_log_space_in_bytes/1024.0/1024 FROM sys.dm_db_log_space_usage;
SET @t = SYSDATETIME();

DELETE FROM Board.T_PURGE WHERE reg_date < '2026-07-01';

SET @r = @@ROWCOUNT;
SELECT @a = used_log_space_in_bytes/1024.0/1024 FROM sys.dm_db_log_space_usage;
PRINT CONCAT(@r, ' 행, ', DATEDIFF(millisecond, @t, SYSDATETIME()), ' ms, 로그 ',
             CAST(@a - @b AS decimal(9,1)), ' MB');

BEGIN TRAN;
DELETE FROM Board.T_PURGE WHERE reg_date >= '2026-07-01';
WAITFOR DELAY '00:00:10';   -- 관찰할 시간을 법니다
ROLLBACK;

SELECT l.resource_type AS 자원, l.request_mode AS 방식, COUNT(*) AS 개수
FROM sys.dm_tran_locks l
WHERE l.resource_database_id = DB_ID() AND l.request_session_id <> @@SPID
GROUP BY l.resource_type, l.request_mode;

-- 지우지 않는 행 하나를 읽어 봅니다.
SET LOCK_TIMEOUT 3000;
SELECT COUNT(*) FROM Board.T_PURGE WHERE num = 1;

DECLARE @n int = 1, @total int = 0;

WHILE @n > 0
BEGIN
    DELETE TOP (4000) FROM Board.T_PURGE WHERE reg_date < '2026-07-01';
    SET @n = @@ROWCOUNT;
    SET @total += @n;
    -- 여기서 트랜잭션이 끝납니다. 잠금이 풀립니다.
END

PRINT CONCAT(@total, ' 행을 지웠습니다.');

-- 같은 26만 639행을 지운 결과입니다.

DELETE FROM Board.T_PURGE;
TRUNCATE TABLE Board.T_PURGE;

BEGIN TRAN;

-- 남길 것만 옮깁니다.
SELECT * INTO Board.T_PURGE_KEEP
FROM Board.T_PURGE WHERE reg_date >= '2026-12-01';

TRUNCATE TABLE Board.T_PURGE;

INSERT INTO Board.T_PURGE SELECT * FROM Board.T_PURGE_KEEP;
DROP TABLE Board.T_PURGE_KEEP;

COMMIT;

CREATE OR ALTER PROCEDURE Board.P_PURGE_OLD
    @before   datetime2(0),
    @batch    int = 3000,
    @max_loop int = 1000
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @n int = 1, @total int = 0, @loop int = 0;

    WHILE @n > 0 AND @loop < @max_loop
    BEGIN
        DELETE TOP (@batch) FROM Board.T_PURGE WHERE reg_date < @before;
        SET @n = @@ROWCOUNT;
        SET @total += @n;
        SET @loop += 1;

        -- 다른 일이 끼어들 틈을 줍니다.
        IF @n > 0 WAITFOR DELAY '00:00:00.100';
    END

    SELECT @total AS 지운행, @loop AS 돈횟수,
           CASE WHEN @loop >= @max_loop THEN N'아직 남았습니다'
                ELSE N'끝났습니다' END AS 상태;
END