데이터베이스 메일 메시지 및 이벤트 로그 보관을 처리하는 SQL Server 에이전트 작업 만들기

아티클
06/13/2017

데이터베이스 메일 메시지 및 해당 첨부 파일의 복사본은 데이터베이스 메일 이벤트 로그와 함께 msdb 테이블에 유지됩니다. 정기적으로 테이블의 크기를 축소하고 더 이상 필요하지 않은 메시지와 이벤트를 보관할 수 있습니다. 다음 절차에서는 프로세스를 자동화하는 SQL Server 에이전트 작업을 만듭니다.

시작하기 전에: , 필수 구성 요소, 권장 사항, 권한
데이터베이스 메일과 로그 보관 시 사용 : SQL Server Agent

시작하기 전에

필수 조건

보관 데이터를 저장할 새 테이블은 특수 보관 데이터베이스에 있을 수 있습니다. 또는 행을 텍스트 파일로 내보낼 수 있습니다.

권장 사항

프로덕션 환경에서 오류 검사를 추가하고 작업이 실패할 경우 운영자에게 전자 메일 메시지를 보낼 수 있습니다.

사용 권한

이 항목에서 설명하는 저장 프로시저를 실행하려면 sysadmin 고정 서버 역할의 멤버여야 합니다.

프로세스 개요

첫 번째 절차에서는 다음 단계를 사용하여 Archive 데이터베이스 메일 작업을 만듭니다.
1. 데이터베이스 메일 테이블의 모든 메시지를 이전 달의 이름을 딴 새 테이블에 DBMailArchive_<year_month> 형식으로 복사합니다.
2. 첫 번째 단계에서 복사된 메시지와 관련된 첨부 파일을 데이터베이스 메일 테이블에서 DBMailArchive_Attachments_<year_month> 형식으로 이전 달 이름에 따라 명명된 새 테이블로 복사합니다.
3. 첫 번째 단계에서 복사한 메시지와 관련된 데이터베이스 메일 이벤트 로그에서 DBMailArchive_Log_<year_month> 형식으로 이전 달 이름을 따라 명명된 새 테이블로 이벤트를 복사합니다.
4. 데이터베이스 메일 테이블에서 전송된 메일 항목의 레코드를 삭제합니다.
5. 데이터베이스 메일 이벤트 로그에서 전송된 메일 항목과 관련된 이벤트를 삭제합니다.
정기적으로 실행되도록 작업을 예약합니다.

SQL Server 에이전트 작업을 만들려면

개체 탐색기에서 SQL Server 에이전트를 확장하고 작업을 마우스 오른쪽 단추로 클릭한 다음 새 작업을 클릭합니다.
새 작업 대화 상자의 이름 상자에 보관 데이터베이스 메일 입력합니다.
소유자 상자에서 소유자가 sysadmin 고정 서버 역할의 구성원인지 확인합니다.
범주 상자에서 데이터베이스 유지 관리를 클릭합니다.
설명 상자에 보관 데이터베이스 메일 메시지를 입력하고 단계를 클릭합니다.

데이터베이스 메일 메시지를 보관하는 단계를 만들려면

단계 페이지에서 새로 만들기를 클릭합니다.
단계 이름 입력란에 Copy Database Mail Items를 입력합니다.
유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.
데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

명령 입력란에 다음 문을 입력하여 이전 달의 이름을 딴 테이블을 만들고 현재 달의 시작일보다 이전인 행을 포함하도록 합니다.

DECLARE @LastMonth nvarchar(12);  
DECLARE @CopyDate nvarchar(20) ;  
DECLARE @CreateTable nvarchar(250) ;  
SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;  
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))  
SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_' + @LastMonth + '] FROM sysmail_allitems WHERE send_request_date < ''' + @CopyDate +'''';  
EXEC sp_executesql @CreateTable ;

OK를 클릭하여 역할을 저장합니다.

데이터베이스 메일 첨부 파일 보관 단계를 만들려면

단계 페이지에서 새로 만들기를 클릭합니다.
단계 이름 상자에 데이터베이스 메일 첨부 파일 복사를 입력합니다.
유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.
데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

명령 상자에 다음 문을 입력하여 이전 단계에서 전송된 메시지에 해당하는 첨부 파일을 포함하는 이전 달의 이름을 따서 명명된 첨부 파일 테이블을 만듭니다.

DECLARE @LastMonth nvarchar(12);  
DECLARE @CopyDate nvarchar(20) ;  
DECLARE @CreateTable nvarchar(250) ;  
SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;  
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))  
SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Attachments_' + @LastMonth + '] FROM sysmail_attachments   
 WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';  
EXEC sp_executesql @CreateTable ;

OK를 클릭하여 역할을 저장합니다.

데이터베이스 메일 로그 보관 단계를 만들려면

단계 페이지에서 새로 만들기를 클릭합니다.
단계 이름 상자에 데이터베이스 메일 로그 복사를 입력합니다.
유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.
데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

명령 상자에 다음 문을 입력하여 이전 단계에서 전송된 메시지에 해당하는 로그 항목을 포함하는 이전 달의 이름을 따서 명명된 로그 테이블을 만듭니다.

DECLARE @LastMonth nvarchar(12);  
DECLARE @CopyDate nvarchar(20) ;  
DECLARE @CreateTable nvarchar(250) ;  
SET @LastMonth = (SELECT CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + '_' + CAST(DATEPART(mm,GETDATE())-1 AS varchar(2))) ;  
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime))  
SET @CreateTable = 'SELECT * INTO msdb.dbo.[DBMailArchive_Log_' + @LastMonth + '] FROM sysmail_Event_Log   
 WHERE mailitem_id in (SELECT DISTINCT mailitem_id FROM [DBMailArchive_' + @LastMonth + '] )';  
EXEC sp_executesql @CreateTable ;

OK를 클릭하여 역할을 저장합니다.

데이터베이스 메일에서 보관된 행을 제거하는 단계를 만들려면

단계 페이지에서 새로 만들기를 클릭합니다.
단계 이름 상자에 데이터베이스 메일에서 행 제거를 입력합니다.
유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.
데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

명령 상자에 다음 문을 입력하여 데이터베이스 메일 테이블에서 현재 월보다 오래된 행을 제거합니다.

DECLARE @CopyDate nvarchar(20) ;  
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;  
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @CopyDate ;

OK를 클릭하여 역할을 저장합니다.

데이터베이스 메일 이벤트 로그에서 보관된 항목을 제거하는 단계를 만들려면

단계 페이지에서 새로 만들기를 클릭합니다.
단계 이름 상자에 데이터베이스 메일 이벤트 로그에서 행 제거를 입력합니다.
유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.

명령 상자에 다음 문을 입력하여 데이터베이스 메일 이벤트 로그에서 현재 월보다 오래된 행을 제거합니다.

DECLARE @CopyDate nvarchar(20) ;  
SET @CopyDate = (SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP- DATEPART(dd,GETDATE()-1), 112) AS datetime)) ;  
EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @CopyDate ;

OK를 클릭하여 역할을 저장합니다.

정기적으로 실행되도록 작업을 예약하려면

새 작업 대화 상자에서 일정을 클릭합니다.
일정 페이지에서 새로 만들기를 클릭합니다.
이름 상자에 보관 데이터베이스 메일 입력합니다.
일정 유형 상자에서 정기를 선택합니다.
빈도 영역에서 작업을 주기적으로 실행하는 옵션(예: 매월 한 번)을 선택합니다.
일별 빈도 영역에서 <time>에 한 번 수행을 선택합니다.
다른 옵션이 원하는 대로 구성되어 있는지 확인한 다음 OK를 클릭하여 일정을 저장합니다.
OK를 클릭하여 작업을 저장합니다.

다음을 통해 공유