분리보관용 서버 구성 (파티셔닝 적용 및 관리)

2021. 9. 14. 21:22

현업의 아래 요구 사항 충족을 위한 구성
1. 라이브환경은 1년 경과 데이터 삭제
2. 1년 경과 데이터는 개인정보 제거후 별도 5년 보관

동작 흐름

스크립트 설명

- 추가설명

SQL Server의 대량 데이터 인입시 Dst 기준, PULL 방식으로 구현해야함
Src의 Push로 할 경우 모든 Row단위 묵시적 커서사용 (수행시간및리소스 사용량 매우 높음)

배치명	하위스탭	스크립트(or SP)명	설명	비고
[DBAs]Inst_ums_log	1~4	WORKDB.dbo.Inst_ums_log	LiveDB_INT → _EXT 순서로 table 테이블단위 359일 전 하루 데이터 인입	SRC 대상 테이블의 스키마정보 (컬럼명/자료형) 조회 및 Insert 쿼리 자동 생성 방식 향후 SRC 컬럼추가되어도 추가 수정 불필요 비식별대상 컬럼일 경우 스크립트내 CASE문 활용하여 데이터 치환 필요
[DBAs]Inst_ums_log	5~6	adhoc	LiveDB_INT/EXT의 DB배치 '[DBAs]del_ums_log' 호출
[DBAs]del_ums_log	09: 1~4 11: 1	WORKDB.dbo.del_ums_log	365일 경과된 table0~4 데이터 삭제 테이블별 1000건 삭제 → delay 0.3 / 최대 일 100만건 삭제
[DBAs]kill_del_batch	1	adhoc	08:00에 delete SP 실행 체크 및 강제 kill
[DBAs]Check_Insert	1	WORKDB.dbo.DBAsP_Insert_Check	Insert 되어야할 Row수 체크 Src의 실제 Row vs. Dst에 인입된 Row수
[DBAs]Create_Partition_1Year	1~2	adhoc	다음해 파티션 생성
[DBAs]Merge_Truncate_Partition_1Month	1~5	adhoc	Log서버내 5년경과된 파티션 Truncate
[DBAs]Merge_Truncate_Partition_1Month	6~7	adhoc	Log서버내 5년경과된 파티션 파티션 merge

스크립트

[DBAs]Inst_ums_log

USE [msdb]
GO
 BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBAs]Inst_table', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'설명이 없습니다.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'table1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec WORKDB.dbo.[Inst_table]  ''LiveDB_EXT'', ''table1''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'table2', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec WORKDB.dbo.[Inst_table]  ''LiveDB_EXT'', ''table2''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'table3', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec WORKDB.dbo.[Inst_table]  ''LiveDB_EXT'', ''table3''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'table4', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec WORKDB.dbo.[Inst_table]  ''LiveDB_EXT'', ''table4''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'del_LIVEDB_EXT', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC [LiveDB_EXT].msdb.dbo.sp_start_job N''[DBAs]]Del_table''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'table', 
		@step_id=6, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec WORKDB.dbo.[Inst_table]  ''LiveDB_INT'', ''table''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'del_LIVEDB_INT', 
		@step_id=7, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC [LIVEDB_INT].msdb.dbo.sp_start_job N''[DBAs]]Del_table'' ', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

WORKDB.dbo.Inst_ums_log

USE [WORKDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--[dbo].[Inst_table]  'LiveDB_INT', 'table'

CREATE proc [dbo].[Inst_table] -- 'LiveDB_INT', 'table'
@dbName nvarchar(100),	@tbName nvarchar(100) 
as
begin  
SET DEADLOCK_PRIORITY LOW
SET NOCOUNT OFF
 			
declare   @idxname nvarchar(100) ,@vsSQL nvarchar(MAX) ,@vsSQL2 nvarchar(max)
DECLARE @error_message 	varchar(max) ='',@out_CNT INT=0  ,@pramdef nvarchar(100) =N'@out_CNT int OUTPUT'

create table #tmp_result 	(dbname nvarchar(max),tbname nvarchar(max),intcnt int)	

if (@dbname+@tbname = 'LiveDB_EXTtable1' or @dbname+@tbname = 'LiveDB_EXTtable2' or 
	@dbname+@tbname = 'LiveDB_EXTtable3' or @dbname+@tbname = 'LiveDB_EXTtable4' or @dbname+@tbname = 'LiveDB_INTtable')
begin 
	set @idxname= replace(@tbName,'ums_','umi_')+'_idx01'

	if (@dbName = 'LiveDB_EXT') begin
			select @vsSQL = 'INSERT INTO ' + @dbname+'.dbo.'+ @tbName + char(10) + '(' + char(10)
			select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' + ',' + char(10)
			 from [LiveDB_EXT].LiveDB_EXT.sys.sysobjects so
			 join [LiveDB_EXT].LiveDB_EXT.sys.syscolumns sc on sc.id = so.id 
			 where so.name = @tbName
			 order by sc.ColID
			 select @vsSQL=substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ') ' +char(10) +'select '
			 select @vsSQL = @vsSQL + ' ' + 
				case when sc.Name in ('PRIVACY_C01','PRIVACY_C02') then '''010-0000-0000'' as ' + sc.Name 
					else sc.Name end + ' ' + ',' + char(10)
			 from [LiveDB_EXT].LiveDB_EXT.sys.sysobjects so
			 join [LiveDB_EXT].LiveDB_EXT.sys.syscolumns sc on sc.id = so.id 
			 where so.name = @tbName
			 order by sc.ColID
			 select @vsSQL=substring(@vsSQL,1,len(@vsSQL) - 2) + char(10)  
			 select @vsSQL= @vsSQL + ' From [LiveDB_EXT].'  + @dbname+'.dbo.'+ @tbName +' '+ char(10) + 
					'WHERE REQUEST_TIME  < CONVERT(VARCHAR(10), DATEADD(D, -(365-7), getdate()), 121) +  '' 00:00:00''  
							 and [REQUEST_TIME]  >= CONVERT(VARCHAR(10), DATEADD(D, -(365-7+1), getdate()), 121) +  '' 00:00:00'''
			select @vsSQL = @vsSQL + char(10) +  ' select @out_CNT =@@rowcount'   
			
			select @vsSQL2 = 'select CONVERT(VARCHAR(10), DATEADD(D, -(365-7+1), getdate()), 121), '''+@tbName+''',count(*)  From [LiveDB_EXT].' + @dbname+'.dbo.'+ @tbName +' '+ char(10) + 
					'WHERE REQUEST_TIME  < CONVERT(VARCHAR(10), DATEADD(D, -(365-7), getdate()), 121) +  '' 00:00:00''
					and [REQUEST_TIME]  >= CONVERT(VARCHAR(10), DATEADD(D, -(365-7+1), getdate()), 121) +  '' 00:00:00'''
		end	 
		else if (@dbName = 'LiveDB_INT') begin
			select @vsSQL = 'INSERT INTO ' + @dbname+'.dbo.'+ @tbName + char(10) + '(' + char(10)
			select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' + ',' + char(10)
			 from [LiveDB_INT].LiveDB_INT.sys.sysobjects so
			 join [LiveDB_INT].LiveDB_INT.sys.syscolumns sc on sc.id = so.id 
			 where so.name = @tbName
			 order by sc.ColID
			 select @vsSQL=substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ') ' +char(10) +'select '
			 select @vsSQL = @vsSQL + ' ' + 
				case when sc.Name in ('PRIVACY_C01','PRIVACY_C02') then '''010-0000-0000'' as ' + sc.Name 
					else sc.Name end + ' ' + ',' + char(10)
			 from [LiveDB_INT].LiveDB_INT.sys.sysobjects so
			 join [LiveDB_INT].LiveDB_INT.sys.syscolumns sc on sc.id = so.id 
			 where so.name = @tbName
			 order by sc.ColID
			 select @vsSQL=substring(@vsSQL,1,len(@vsSQL) - 2) + char(10)  
			 select @vsSQL= @vsSQL + ' From [LiveDB_INT].'  + @dbname+'.dbo.'+ @tbName + char(10) + 
					'WHERE REQUEST_TIME  < CONVERT(VARCHAR(10), DATEADD(D, -(365-7), getdate()), 121) +  '' 00:00:00''
							 and [REQUEST_TIME]  >= CONVERT(VARCHAR(10), DATEADD(D, -(365-7+1), getdate()), 121) +  '' 00:00:00'''
			 select @vsSQL = @vsSQL + char(10) +  ' select @out_CNT =@@rowcount'   
	         
			 select @vsSQL2 = 'select CONVERT(VARCHAR(10), DATEADD(D, -(365-7+1), getdate()), 121), '''+@tbName+''',count(*)  From [LiveDB_INT].' + @dbname+'.dbo.'+ @tbName + char(10) + 
					'WHERE REQUEST_TIME  < CONVERT(VARCHAR(10), DATEADD(D, -(365-7), getdate()), 121) +  '' 00:00:00''
					and [REQUEST_TIME]  >= CONVERT(VARCHAR(10), DATEADD(D, -(365-7+1), getdate()), 121) +  '' 00:00:00'''
		end	 
	exec  sp_executesql @vsSQL,@pramdef,  @out_CNT OUTPUT  
	 set @error_message= '[Inst. '+@dbName+'.dbo.'+@tbName+ ' || '+cast( @out_CNT as varchar(100)) +']'
	 print @vsSQL
	 print @vsSQL2
     
     -- sp_executesql의 @out_CNT을 받을 수 있지만 확실하게 직접 COUNT한 값을 체크테이블에 인입함
	insert into #tmp_result 
	exec (@vsSQL2) 
	insert into [LOGDB01].WORKDB.dbo.[DBAt_Insert_Check]
	 
end
else 
begin
	set @error_message= '[Report || Invalidate Parameter]' +char(10)+char(13)  
	GOTO OnError
end 
 
   



/*******************************************************************************
*  WORK COMPLETE
********************************************************************************/
IF @@TRANCOUNT > 0 COMMIT TRAN
PRINT (@error_message)
RETURN 0

/*******************************************************************************
* Error Handling
********************************************************************************/
OnError:
IF @@TRANCOUNT > 0 ROLLBACK TRAN
PRINT (@error_message)
RAISERROR(@error_message, 16, 1)
RETURN (1*(-1))

 
end
GO

[DBAs]del_ums_log

USE [msdb]
GO

/****** Object:  Job [[DBAs]]Del_table]    Script Date: 08/31/2021 09:13:34 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/31/2021 09:13:35 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBAs]Del_table', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'설명이 없습니다.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Del_table]    Script Date: 08/31/2021 09:13:35 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Del_table', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec WORKDB..[Del_table] ''LiveDB_INT'',''table''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

WORKDB.dbo.del_ums_log

USE [WORKDB]
GO

 Object  StoredProcedure [dbo].[Del_table]    Script Date 08312021 091037 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE proc [dbo].[Del_table] --'LiveDB_INT','tt'
@dbName nvarchar(100),	@tbName nvarchar(100)
as
begin  
SET DEADLOCK_PRIORITY LOW
SET NOCOUNT ON
DECLARE @CurrDate datetime, @DeleteDate DATETIME --,@TargetDate DATETIME 
DECLARE @LimitDel int=0,@RCnt int=99999
DECLARE @error_message 	varchar(max) =''
 

SET @CurrDate = GETDATE()
 
set @DeleteDate= CONVERT(VARCHAR(10), DATEADD(DD, -(365), @CurrDate), 121) +  ' 000000' 
 
--삭제 작업(반복)
if @dbName='LiveDB_EXT' and @tbName='table1'
begin
	while ( @RCnt0  and @LimitDel 10000000 )
	begin
		  delete top (1000)
		from LiveDB_EXT..table1 
		where  REQUEST_TIME@DeleteDate
		option(maxdop 1)
	    
		select @RCnt=@@ROWCOUNT
		select @LimitDel = @LimitDel+@RCnt  
	     
		waitfor delay '000000.300'
	end
	
	set @error_message = 'Del. ['+@tbName+']  '+ cast(@LimitDel as varchar(10)) +char(10)+char(13)
end
else if @dbName='LiveDB_EXT' and @tbName='table2'
begin
	while ( @RCnt0  and @LimitDel 1000000 )
	begin
		  delete top (1000)
		from LiveDB_EXT..table2 
		where  REQUEST_TIME@DeleteDate
		option(maxdop 1)
	    
		select @RCnt=@@ROWCOUNT
		select @LimitDel = @LimitDel+@RCnt  
	          
		waitfor delay '000000.300'
	end
	
	set @error_message = 'Del. ['+@tbName+']  '+ cast(@LimitDel as varchar(10)) +char(10)+char(13)
end
else if @dbName='LiveDB_EXT' and @tbName='table3'
begin
	while ( @RCnt0  and @LimitDel 1000000 )
	begin
		  delete top (1000)
		from LiveDB_EXT..table3 
		where  REQUEST_TIME@DeleteDate
		option(maxdop 1)
	    
		select @RCnt=@@ROWCOUNT
		select @LimitDel = @LimitDel+@RCnt  
	     
		waitfor delay '000000.300'   
	end
	
	set @error_message = 'Del. ['+@tbName+']  '+ cast(@LimitDel as varchar(10)) +char(10)+char(13)
end
else if @dbName='LiveDB_EXT' and @tbName='table4'
begin
	while ( @RCnt0  and @LimitDel 1000000 )
	begin
		  delete top (1000)
		from LiveDB_EXT..table4
		where  REQUEST_TIME@DeleteDate
		option(maxdop 1)
	    
		select @RCnt=@@ROWCOUNT
		select @LimitDel = @LimitDel+@RCnt  
	      
		waitfor delay '000000.300'   
	end
	
	set @error_message = 'Del. ['+@tbName+']  '+ cast(@LimitDel as varchar(10)) +char(10)+char(13)
end
else if @dbName='LiveDB_INT' and @tbName='table'
begin
	while ( @RCnt0  and @LimitDel 1000000 )
	begin
		  delete top (1000)
		from LiveDB_INT..table
		where  REQUEST_TIME@DeleteDate
		option(maxdop 1)
	    
		select @RCnt=@@ROWCOUNT
		select @LimitDel = @LimitDel+@RCnt  
	      
		waitfor delay '000000.300'   
	end
	
	set @error_message = 'Del. ['+@tbName+']  '+ cast(@LimitDel as varchar(10)) +char(10)+char(13)
end
else 
begin
	set @error_message= '[Report  Invalidate Parameter]' +char(10)+char(13)  
	GOTO OnError
end 
 
   




  WORK COMPLETE

IF @@TRANCOUNT  0 COMMIT TRAN
PRINT (@error_message)
RETURN 0


 Error Handling

OnError
IF @@TRANCOUNT  0 ROLLBACK TRAN
PRINT (@error_message)
RAISERROR(@error_message, 16, 1)
RETURN (1(-1))

 
end
GO

[DBAs]Check_Insert

USE [msdb]
GO

/****** Object:  Job [[DBAs]Check_Insert]    Script Date: 2021-09-01 오전 7:40:45 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2021-09-01 오전 7:40:45 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBAs]Check_Insert', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'설명이 없습니다.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [-1 Y]    Script Date: 2021-09-01 오전 7:40:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'-1 Y', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec [dbo].[DBAsP_Insert_Check]', 
		@database_name=N'WORKDB', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'매일 08시', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20210902, 
		@active_end_date=99991231, 
		@active_start_time=80000, 
		@active_end_time=235959, 
		@schedule_uid=N'b8f6e710-393a-4bba-a3ed-e1ea22fae5a0'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

[DBAs]Create_Partition_1Year

USE [msdb]
GO

/****** Object:  Job [[DBAs]Create_Partition_1Year]    Script Date: 2021-09-01 오전 7:40:48 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2021-09-01 오전 7:40:48 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBAs]Create_Partition_1Year', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'설명이 없습니다.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [LiveDB_EXT]    Script Date: 2021-09-01 오전 7:40:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'LiveDB_EXT', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'  declare @begin_date datetime, @end_date datetime, @value int, @i int, @SQL nvarchar(4000) 
  
    -- 다음년 1월
    SET @begin_date = CONVERT(VARCHAR(5), DATEADD(YY,  1, GETDATE()), 23) + ''01-01''
    -- 다음년 12월
    SET @end_date = CONVERT(VARCHAR(10), DATEADD(MONTH, 11, @begin_date)   , 23)
  
  select @begin_date,@end_date
    SET @i = 0
    SET @value = datediff(MONTH, @begin_date, @end_date)
  
   
    WHILE (@i <= @value)
    BEGIN
        select @SQL = ''ALTER PARTITION SCHEME PARTITION_SH NEXT USED [PARTITON_GROUP]; ALTER PARTITION FUNCTION PARTITION_FN() SPLIT RANGE('''''' + CONVERT(char(10),dateadd(MM,@i,@begin_date),120) + '''''');''
        from master.dbo.spt_values
        where type = ''P'' and number <= datediff(d, @begin_date, @end_date)
  
        SET @i = @i + 1
       exec @SQL
    END', 
		@database_name=N'LiveDB_EXT', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [LiveDB_INT]    Script Date: 2021-09-01 오전 7:40:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'LiveDB_INT', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'  declare @begin_date datetime, @end_date datetime, @value int, @i int, @SQL nvarchar(4000) 
  
    -- 다음년 1월
    SET @begin_date = CONVERT(VARCHAR(5), DATEADD(YY,  1, GETDATE()), 23) + ''01-01''
    -- 다음년 12월
    SET @end_date = CONVERT(VARCHAR(10), DATEADD(MONTH, 11, @begin_date)   , 23)
  
  select @begin_date,@end_date
    SET @i = 0
    SET @value = datediff(MONTH, @begin_date, @end_date)
  
   
    WHILE (@i <= @value)
    BEGIN
        select @SQL = ''ALTER PARTITION SCHEME PARTITION_SH NEXT USED [PARTITON_GROUP]; ALTER PARTITION FUNCTION PARTITION_FN() SPLIT RANGE('''''' + CONVERT(char(10),dateadd(MM,@i,@begin_date),120) + '''''');''
        from master.dbo.spt_values
        where type = ''P'' and number <= datediff(d, @begin_date, @end_date)
  
        SET @i = @i + 1
       exec @SQL
    END', 
		@database_name=N'LiveDB_INT',  
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'매년 12월 1일', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=12, 
		@active_start_date=20211201, 
		@active_end_date=99991231, 
		@active_start_time=90000, 
		@active_end_time=235959, 
		@schedule_uid=N'65e76669-5ecf-4ec4-8c24-9b86ad41a34f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

[DBAs]Merge_Truncate_Partition_1Month

USE [msdb]
GO

/****** Object:  Job [[DBAs]Merge_Truncate_Partition_1Month]    Script Date: 2021-09-01 오전 7:40:56 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2021-09-01 오전 7:40:56 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBAs]Merge_Truncate_Partition_1Month', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'설명이 없습니다.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate_LiveDB_EXT_table1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--파티션 truncate / merge (매월 1일 수행)
	declare @begin_date datetime ,@target_date datetime , @SQL nvarchar(4000),@partition_number int, @SQL2 nvarchar(4000)
  
	SET @begin_date = CONVERT(VARCHAR(8), DATEADD(mm,  -61, GETDATE()), 23) + ''01''
	
	SELECT  @partition_number= ISNULL(p.partition_number,0),@target_date=cast(value as datetime)
    FROM sys.tables AS t
	   JOIN sys.indexes AS i ON t.object_id = i.object_id
	   JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
	   JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
	   JOIN sys.partition_functions AS f ON s.function_id = f.function_id
	   LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
   WHERE t.name = ''table1'' AND i.type <= 1
		AND r.value <= @begin_date
   
    --파티션 Truncate로 데이터 삭제
    IF (@partition_number > 1)
    BEGIN
		SET @SQL = ''TRUNCATE TABLE table1 WITH (PARTITIONS ('' + CAST (@partition_number AS VARCHAR) + ''));''
		exec (@SQL)
    END 
', 
		@database_name=N'LiveDB_EXT', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate_LiveDB_EXT_table2', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--파티션 truncate / merge (매월 1일 수행)

	declare @begin_date datetime ,@target_date datetime , @SQL nvarchar(4000),@partition_number int, @SQL2 nvarchar(4000)
  
	SET @begin_date = CONVERT(VARCHAR(8), DATEADD(mm,  -61, GETDATE()), 23) + ''01''
	
	SELECT  @partition_number= ISNULL(p.partition_number,0),@target_date=cast(value as datetime)
    FROM sys.tables AS t
	   JOIN sys.indexes AS i ON t.object_id = i.object_id
	   JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
	   JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
	   JOIN sys.partition_functions AS f ON s.function_id = f.function_id
	   LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
   WHERE t.name = ''table2'' AND i.type <= 1
		AND r.value <= @begin_date
   
    --파티션 Truncate로 데이터 삭제
    IF (@partition_number > 1)
    BEGIN
		SET @SQL = ''TRUNCATE TABLE table2 WITH (PARTITIONS ('' + CAST (@partition_number AS VARCHAR) + ''));''
		exec (@SQL)
    END 
 


', 
		@database_name=N'LiveDB_EXT', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate_LiveDB_EXT_table3', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'	declare @begin_date datetime ,@target_date datetime , @SQL nvarchar(4000),@partition_number int, @SQL2 nvarchar(4000)
  
	SET @begin_date = CONVERT(VARCHAR(8), DATEADD(mm,  -61, GETDATE()), 23) + ''01''
	
	SELECT  @partition_number= ISNULL(p.partition_number,0),@target_date=cast(value as datetime)
    FROM sys.tables AS t
	   JOIN sys.indexes AS i ON t.object_id = i.object_id
	   JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
	   JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
	   JOIN sys.partition_functions AS f ON s.function_id = f.function_id
	   LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
   WHERE t.name = ''table3'' AND i.type <= 1
		AND r.value <= @begin_date
   
    --파티션 Truncate로 데이터 삭제
    IF (@partition_number > 1)
    BEGIN
		SET @SQL = ''TRUNCATE TABLE table3 WITH (PARTITIONS ('' + CAST (@partition_number AS VARCHAR) + ''));''
		exec (@SQL)
    END', 
		@database_name=N'LiveDB_EXT', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Truncate_LiveDB_EXT_table4', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'	declare @begin_date datetime ,@target_date datetime , @SQL nvarchar(4000),@partition_number int, @SQL2 nvarchar(4000)
  
	SET @begin_date = CONVERT(VARCHAR(8), DATEADD(mm,  -61, GETDATE()), 23) + ''01''
	
	SELECT  @partition_number= ISNULL(p.partition_number,0),@target_date=cast(value as datetime)
    FROM sys.tables AS t
	   JOIN sys.indexes AS i ON t.object_id = i.object_id
	   JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
	   JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
	   JOIN sys.partition_functions AS f ON s.function_id = f.function_id
	   LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
   WHERE t.name = ''table4'' AND i.type <= 1
		AND r.value <= @begin_date
   
    --파티션 Truncate로 데이터 삭제
    IF (@partition_number > 1)
    BEGIN
		SET @SQL = ''TRUNCATE TABLE table4 WITH (PARTITIONS ('' + CAST (@partition_number AS VARCHAR) + ''));''
		exec (@SQL)
    END', 
		@database_name=N'LiveDB_EXT', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Merge_LiveDB_EXT', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--파티션 truncate / merge (매월 1일 수행)

	declare @begin_date datetime ,@target_date datetime , @SQL nvarchar(4000),@partition_number int, @SQL2 nvarchar(4000)
  
	SET @begin_date = CONVERT(VARCHAR(8), DATEADD(mm,  -61, GETDATE()), 23) + ''01''
	
	SELECT  @partition_number= ISNULL(p.partition_number,0),@target_date=cast(value as datetime)
    FROM sys.tables AS t
	   JOIN sys.indexes AS i ON t.object_id = i.object_id
	   JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
	   JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
	   JOIN sys.partition_functions AS f ON s.function_id = f.function_id
	   LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
   WHERE t.name = ''table1'' AND i.type <= 1
		AND r.value <= @begin_date
    
    --데이터 삭제한 파티션 MERGE
	IF (@target_date <> ''1900-01-01'')
	BEGIN
		SET @SQL2 = ''ALTER PARTITION FUNCTION PARTITION_FN() MERGE RANGE(''''''+convert(nvarchar(10),@target_date,23)+'''''');''
		exec (@SQL2)
	END  


', 
		@database_name=N'LiveDB_EXT', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'LiveDB_INT', 
		@step_id=6, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--파티션 truncate / merge (매월 1일 수행)
	declare @begin_date datetime ,@target_date datetime , @SQL nvarchar(4000),@partition_number int, @SQL2 nvarchar(4000)
  
	SET @begin_date = CONVERT(VARCHAR(8), DATEADD(mm,  -61, GETDATE()), 23) + ''01''
	
	SELECT  @partition_number= ISNULL(p.partition_number,0),@target_date=cast(value as datetime)
    FROM sys.tables AS t
	   JOIN sys.indexes AS i ON t.object_id = i.object_id
	   JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
	   JOIN  sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
	   JOIN sys.partition_functions AS f ON s.function_id = f.function_id
	   LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id and r.boundary_id = p.partition_number
   WHERE t.name = ''table'' AND i.type <= 1
		AND r.value <= @begin_date
   
    --파티션 Truncate로 데이터 삭제
    IF (@partition_number > 1)
    BEGIN
		SET @SQL = ''TRUNCATE TABLE table WITH (PARTITIONS ('' + CAST (@partition_number AS VARCHAR) + ''));''
		exec (@SQL)
    END 
    --데이터 삭제한 파티션 MERGE
	IF (@target_date <> ''1900-01-01'')
	BEGIN
		SET @SQL2 = ''ALTER PARTITION FUNCTION PARTITION_FN() MERGE RANGE(''''''+convert(nvarchar(10),@target_date,23)+'''''');''
		exec (@SQL2)
	END  
', 
		@database_name=N'LiveDB_INT', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1개월마다', 
		@enabled=1, 
		@freq_type=32, 
		@freq_interval=2, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=1, 
		@freq_recurrence_factor=1, 
		@active_start_date=20210812, 
		@active_end_date=99991231, 
		@active_start_time=70000, 
		@active_end_time=235959, 
		@schedule_uid=N'43151ddc-91d5-40a8-8e00-2897a429979f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

저작자표시 비영리 변경금지

'SQL Server > 테스트 (+스크립트)' 카테고리의 다른 글

[테스트] Checkpoint IO제한과 Indirect /direct 비교 (0)	2022.03.15
통계용 DB (스크립트 기반 Logshipping) 구성 (0)	2021.09.14
도메인 독립 AlwaysOn 구성 (AD 미조인) (0)	2021.06.27
AlwaysOn with Basic 도입 방안 (0)	2021.06.26
Alwayson 구성 (0)	2021.06.26

일상및기술기록

분리보관용 서버 구성 (파티셔닝 적용 및 관리)

동작 흐름

스크립트 설명

스크립트

'SQL Server > 테스트 (+스크립트)' 카테고리의 다른 글

+ Recent posts

티스토리툴바