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

2025-05-20

적용 대상:SQL Server Azure SQL Managed Instance

데이터베이스 메일 메시지 및 해당 첨부 파일의 복사본은 데이터베이스 메일 이벤트 로그와 함께 테이블에 유지 msdb 됩니다. 정기적으로 테이블의 크기를 축소하고 더 이상 필요하지 않은 메시지와 이벤트를 보관할 수 있습니다.

다음 절차에서는 프로세스를 자동화하는 SQL Server 에이전트 작업을 만듭니다.

필수 조건

SQL Server 인스턴스에서 T-SQL 명령을 실행하려면 SSMS(SQL Server Management Studio),Visual Studio Code용 MSSQL 확장, sqlcmd 또는 즐겨 찾는 T-SQL 쿼리 도구를 사용합니다.

권장 사항

오류 검사를 고려하고 이 보관 작업이 실패하는 경우 운영자에게 전자 메일 메시지를 보내도록 이 작업을 모니터링합니다.

필요에 따라 보관된 데이터베이스 메일 데이터를 외부의 msdb사용자 지정 보관 데이터베이스로 이동하거나 SQL Server에서 내보낼 수 있습니다.

사용 권한

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

보관 데이터베이스 메일 작업 만들기

첫 번째 절차에서는 다음 단계를 사용하여 Archive 데이터베이스 메일 작업을 만듭니다.

데이터베이스 메일 테이블의 모든 메시지를 이전 달의 이름을 딴 새 테이블에 형식 DBMailArchive__<year_month>으로 복사합니다.
첫 번째 단계에서 복사한 메시지와 관련된 첨부 파일을 데이터베이스 메일 테이블에서 이전 달의 이름을 딴 새 테이블 형식 DBMailArchive_Attachments_<year_month>으로 복사합니다.
첫 번째 단계에서 복사한 메시지와 관련된 데이터베이스 메일 이벤트 로그의 이벤트를 데이터베이스 메일 테이블에서 이전 달의 이름을 딴 새 테이블 형식 DBMailArchive_Log_<year_month>으로 복사합니다.
데이터베이스 메일 테이블에서 전송된 메일 항목의 레코드를 삭제합니다.
데이터베이스 메일 이벤트 로그에서 전송된 메일 항목과 관련된 이벤트를 삭제합니다.
정기적으로 실행되도록 작업을 예약합니다.

SQL Server 에이전트 작업 만들기

다음 단계에서는 SSMS(SQL Server Management Studio)를 사용합니다. aka.ms/ssms 최신 버전의 SSMS를 다운로드합니다.

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

데이터베이스 메일 메시지를 보관하는 작업 단계 만들기

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

명령 상자에 다음 T-SQL 문을 입력하여 이전 달의 이름을 따서 명명된 테이블을 만들고 현재 월의 시작보다 오래된 행을 포함합니다.

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 ;

확인을 선택하여 단계를 저장합니다.

데이터베이스 메일 첨부 파일을 보관하는 작업 단계 만들기

단계 페이지에서 새로 만들기를 선택합니다.
단계 이름 상자에 데이터베이스 메일 첨부 파일 복사를 입력합니다.
유형 드롭다운 목록에서 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 ;

확인을 선택하여 단계를 저장합니다.

데이터베이스 메일 로그를 보관하는 작업 단계 만들기

단계 페이지에서 새로 만들기를 선택합니다.
단계 이름 상자에 데이터베이스 메일 로그 복사를 입력합니다.
유형 드롭다운 목록에서 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 ;

확인을 선택하여 단계를 저장합니다.

데이터베이스 메일에서 보관된 행을 제거하는 작업 단계 만들기

단계 페이지에서 새로 만들기를 선택합니다.
단계 이름 상자에 데이터베이스 메일에서 행 제거를 입력합니다.
유형 드롭다운 목록에서 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 ;

확인을 선택하여 단계를 저장합니다.

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

단계 페이지에서 새로 만들기를 선택합니다.
단계 이름 상자에 데이터베이스 메일 이벤트 로그에서 행 제거를 입력합니다.
유형 드롭다운 목록에서 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 ;

확인을 선택하여 단계를 저장합니다.

주기적으로 실행되도록 작업 예약

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

일반 데이터베이스 메일 문제 해결 단계