로그 테이블이 너무 많아졌을 때 작업스케줄러에 해당 프로시저를 매일 일정시간에 한 번 돌리는 방법을 생각해볼 수 있다.

한 번 쿼리를 날릴 때 하루 치 데이터를 삭제한다고 가정하고,

삭제할 횟수와, 삭제하는 시간을 옵션으로 줄 수 있을 것이다.

INIT

-- 배치 테이블
IF OBJECT_ID('DBO.TCM_BATCH_LOG_MANAGE') IS NOT NULL
        DROP TABLE TCM_BATCH_LOG_MANAGE
CREATE TABLE TCM_BATCH_LOG_MANAGE
(
        SRC_TABLE NVARCHAR(200) NOT NULL
        , BASE_DEL_COL NVARCHAR(100) NOT NULL -- 데이터를 삭제할 때 사용할 날짜형식의 칼럼명
        , DEL_PERIOD INT NOT NULL -- 삭제기간
        , DEL_UNIT NVARCHAR(5) NOT NULL -- 삭제기간 단위 YEAR, MONTH
        , IS_TYPE_TC CHAR(1) NOT NULL -- T 타임, C 횟수
        , END_COND_CNT_SEC INT DEFAULT 0 NOT NULL-- N초 동안 또는 N회, 하루치씩의 데이터를 계속해서 지운다
        , IS_MOVE_YN CHAR(1) NOT NULL -- 로그 데이터를 다른 테이블로 이동할건지
        , DEST_TABLE NVARCHAR(200) -- 어느테이블로 이동할 것인지
        , DEST_COL NVARCHAR(4000) -- 목적지 테이블에 INSERT 할 COLUMNS
        , SELECT_COL NVARCHAR(4000) -- 로그 테이블에서 가져올 COLUMNS
        , IS_USE_YN CHAR(1) DEFAULT 'N'
        , LAST_RUNTIME DATETIME -- 마지막 실행시간
        , RUNTIME DECIMAL(10,3) -- 실행시간
        , RESULT NVARCHAR(5) -- 결과
        , AFFECTED_ROWS INT -- 영향받은 행 수
        , ERROR_MSG NVARCHAR(4000) -- 에러 메시지
)
GO
ALTER TABLE TCM_BATCH_LOG_MANAGE ADD CONSTRAINT CHK_SEC CHECK (END_COND_CNT_SEC > -1 )
ALTER TABLE TCM_BATCH_LOG_MANAGE ADD CONSTRAINT CHK_USE CHECK (IS_USE_YN IN ('Y', 'N'))
ALTER TABLE TCM_BATCH_LOG_MANAGE ADD CONSTRAINT CHK_TYPE CHECK (IS_TYPE_TC IN ('T', 'C'))
ALTER TABLE TCM_BATCH_LOG_MANAGE ADD CONSTRAINT CHK_MOVE CHECK (IS_MOVE_YN IN ('Y', 'N'))
ALTER TABLE TCM_BATCH_LOG_MANAGE ADD CONSTRAINT CHK_PERIOD CHECK (DEL_PERIOD < 0)
ALTER TABLE TCM_BATCH_LOG_MANAGE ADD CONSTRAINT CHK_UNIT CHECK (DEL_UNIT IN ('YEAR', 'MONTH'))
ALTER TABLE [DBO].[TCM_BATCH_LOG_MANAGE] ADD CONSTRAINT [PK_TCM_BATCH_LOG_MANAGE] PRIMARY KEY CLUSTERED
(
        [SRC_TABLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- 데이터 이동 테스트
DROP TABLE LOG_TEST
CREATE TABLE LOG_TEST
(
        LOG_ID INT
        , ASSET_ID INT
)
--SAMPLE DATA INSERT
INSERT INTO TCM_BATCH_LOG_MANAGE(SRC_TABLE, BASE_DEL_COL, DEL_PERIOD, DEL_UNIT, IS_TYPE_TC, END_COND_CNT_SEC, IS_MOVE_YN, DEST_TABLE, DEST_COL, SELECT_COL, IS_USE_YN)
VALUES
('TAS_ASSET_HISTORY', 'CREATE_DATE', -1, 'YEAR', 'C', 2 , 'Y', 'LOG_TEST', 'LOG_ID, ASSET_ID', 'LOG_ID, ASSET_ID', 'Y')

SP

CREATE PROCEDURE BATCH_LOG_MANAGE
AS
BEGIN
        DECLARE @SRC_TABLE NVARCHAR(200), @BASE_DEL_COL NVARCHAR(100), @DEL_PERIOD INT, @DEL_UNIT NVARCHAR(5);
        DECLARE @IS_TYPE_TC CHAR(1), @IS_MOVE_YN CHAR(1)
        DECLARE @END_COND INT, @CUR_END_SECOND DATETIME, @CUR_COUNT INT; -- END_CONDITION_MINUTE를 DATEADD 로 계산하여 넣어둠
        DECLARE @DEST_TABLE NVARCHAR(200), @DEST_COL NVARCHAR(4000), @SELECT_COL NVARCHAR(4000), @IS_DEST_TABLE NVARCHAR(200)
        -- 지울 때는 하루치씩 지운다
        DECLARE @DEL_DATE DATETIME  -- 삭제할 날짜
        DECLARE @SQL NVARCHAR(4000)
        DECLARE @PARAMS NVARCHAR(4000)
        DECLARE @AFFECTED_ROWS INT
        -- 루프 변수
        DECLARE @I INT, @MAX_I INT      
        DECLARE @DEL_FLAG BIT
        DECLARE @STIME DATETIME
        if OBJECT_ID('TEMPDB..#TMP_MANAGER') is not null
               DROP TABLE #TMP_MANAGER
        SELECT ROW_NUMBER() OVER(ORDER BY SRC_TABLE) AS ID, *
        INTO #TMP_MANAGER
        FROM TCM_BATCH_LOG_MANAGE
        WHERE IS_USE_YN = 'Y'
        SELECT @I = 1, @MAX_I = MAX(ID) FROM #TMP_MANAGER
        WHILE @I <= @MAX_I
        BEGIN
               SELECT @SRC_TABLE = SRC_TABLE, @BASE_DEL_COL = BASE_DEL_COL, @DEL_PERIOD = DEL_PERIOD, @DEL_UNIT = DEL_UNIT, @IS_TYPE_TC = IS_TYPE_TC,
                       @END_COND = END_COND_CNT_SEC, @IS_MOVE_YN = IS_MOVE_YN,
                       @DEST_TABLE = DEST_TABLE, @DEST_COL = DEST_COL, @SELECT_COL = SELECT_COL,
                       -- 아래 삭제용 WHILE문 용 변수 초기화
                       @DEL_FLAG = 1, @CUR_COUNT = 0, @CUR_END_SECOND = DATEADD(SS, END_COND_CNT_SEC, GETDATE()), @AFFECTED_ROWS = 0, @STIME = GETDATE()
               FROM #TMP_MANAGER
               WHERE ID = @I
               --마지막 실행시간
               UPDATE TCM_BATCH_LOG_MANAGE SET LAST_RUNTIME = GETDATE(), RUNTIME = NULL, RESULT = NULL, AFFECTED_ROWS = 0, ERROR_MSG = NULL WHERE SRC_TABLE = @SRC_TABLE
               -- 실제 지우는 WHILE 문
               WHILE 1 = 1
               BEGIN
                       BEGIN TRY
                              SET @SQL = 'SELECT @DEL_DATE = CAST(DATEADD(DD, 1, MIN(' + @BASE_DEL_COL + ')) AS DATE) FROM ' + @SRC_TABLE
                                                      + ' WHERE ' + @BASE_DEL_COL + ' < DATEADD(' + @DEL_UNIT + ', ' + CONVERT(NVARCHAR(10), @DEL_PERIOD) + ', GETDATE())';
                              SET @PARAMS = '@DEL_DATE DATETIME OUTPUT'
                              EXEC SP_EXECUTESQL @SQL, @PARAMS, @DEL_DATE OUTPUT;
                              IF(@DEL_DATE IS NULL)
                              BEGIN
                                      BREAK
                              END
                              IF @IS_MOVE_YN = 'Y'
                              BEGIN
                                      -- 목적지 테이블 검색
                                      SET @SQL = 'SELECT @IS_DEST_TABLE = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''DBO'' AND  TABLE_NAME = @DEST_TABLE'
                                      SET @PARAMS = '@DEST_TABLE NVARCHAR(200), @IS_DEST_TABLE NVARCHAR(200) OUTPUT'
                                      EXEC SP_EXECUTESQL @SQL, @PARAMS, @DEST_TABLE, @IS_DEST_TABLE OUTPUT;
                                      IF @IS_DEST_TABLE IS NULL
                                      BEGIN
                                              UPDATE TCM_BATCH_LOG_MANAGE SET ERROR_MSG = '복사할 목적지 테이블이 없습니다' WHERE SRC_TABLE = @SRC_TABLE
                                              SET @DEL_FLAG = 0
                                              BREAK
                                      END
                                      -- 데이터 복사
                                      SET @SQL = 'INSERT INTO ' + @DEST_TABLE + '('+ @DEST_COL + ') SELECT ' + @SELECT_COL + ' FROM ' + @SRC_TABLE + ' WHERE ' + @BASE_DEL_COL + ' < @DEL_DATE'
                                      SET @PARAMS = '@DEL_DATE DATETIME'
                                      EXEC SP_EXECUTESQL @SQL, @PARAMS, @DEL_DATE;
                              END
                              SET @SQL = 'DELETE FROM ' + @SRC_TABLE + ' WHERE ' + @BASE_DEL_COL + ' < @DEL_DATE'
                              SET @PARAMS = '@DEL_DATE DATETIME'
                              EXEC SP_EXECUTESQL @SQL, @PARAMS, @DEL_DATE;
                              --영향받은 행수
                              SET @AFFECTED_ROWS += @@ROWCOUNT
                       END TRY
                       BEGIN CATCH
                              UPDATE TCM_BATCH_LOG_MANAGE SET ERROR_MSG = ERROR_MESSAGE() WHERE SRC_TABLE = @SRC_TABLE
                              SET @DEL_FLAG = 0
                              BREAK
                       END CATCH
                       SET @CUR_COUNT += 1
                       IF @IS_TYPE_TC = 'T' AND GETDATE() >= @CUR_END_SECOND
                              BREAK
                       ELSE IF @IS_TYPE_TC = 'C' AND @CUR_COUNT >= @END_COND
                              BREAK
               END
               UPDATE TCM_BATCH_LOG_MANAGE SET RESULT = (CASE WHEN @DEL_FLAG = 1 THEN '성공' ELSE '실패' END), AFFECTED_ROWS = @AFFECTED_ROWS
                       , RUNTIME = DATEDIFF(MILLISECOND, @STIME, GETDATE()) /  CONVERT(FLOAT,1000)
               WHERE SRC_TABLE = @SRC_TABLE
               SET @I += 1
        END
        DROP TABLE #TMP_MANAGER
END

[MSSQL] 로그테이블 정리