로그 테이블이 너무 많아졌을 때 작업스케줄러에 해당 프로시저를 매일 일정시간에 한 번 돌리는 방법을 생각해볼 수 있다.
한 번 쿼리를 날릴 때 하루 치 데이터를 삭제한다고 가정하고,
삭제할 횟수와, 삭제하는 시간을 옵션으로 줄 수 있을 것이다.
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
