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

데이터베이스 메일 및 첨부 파일의 복사본은 데이터베이스 메일 이벤트 로그와 함께 msdb 테이블에 보관됩니다. 주기적으로 테이블의 크기를 줄이고 더 이상 필요하지 않은 메시지와 이벤트를 제거해야 하는 경우가 있습니다. 다음 절차에서는 SQL Server 에이전트 작업을 만들어 이 프로세스를 자동화합니다.

1. 처음 절차에서는 5단계를 가진 Archive Database Mail이라는 작업을 만듭니다.

2. 첫 번째 단계에서는 데이터베이스 메일 테이블의 모든 메시지를 이름이 DBMailArchive_<year_month> 형식인 새 테이블에 복사합니다. 뒤에 붙는 이름은 이전 달에 해당합니다.

3. 2번째 단계에서는 첫 번째 단계에서 복사한 메시지와 관련이 있는 데이터베이스 메일 테이블의 첨부 파일을 이름이 DBMailArchive_Attachments_<year_month> 형식인 새 테이블에 복사합니다. 뒤에 붙는 이름은 이전 달에 해당합니다.

4. 3번째 단계에서는 첫 번째 단계에서 복사한 메시지와 관련이 있는 데이터베이스 메일 이벤트 로그의 이벤트를 데이터베이스 메일 테이블에서 이름이 DBMailArchive_Log_<year_month> 형식인 새 테이블로 복사합니다. 뒤에 붙는 이름은 이전 달에 해당합니다.

5. 4번째 단계에서는 지금까지 복사한 메일 항목의 레코드를 데이터베이스 메일 테이블에서 삭제합니다.

6. 5번째 단계에서는 복사한 메일 항목과 관련된 이벤트를 데이터베이스 메일 이벤트 로그에서 삭제합니다.

7. 마지막 절차에서는 각 달이 시작될 때 실행할 작업을 예약합니다.

이 예에서는 msdb 데이터베이스에 보관 테이블을 만듭니다. msdb 데이터베이스의 크기를 줄이려면 새 테이블을 특별한 보관 데이터베이스에 저장하거나, 행을 텍스트 파일로 내보내거나 삭제하십시오. 이 예에서는 단순히 행을 msdb 데이터베이스의 새 테이블로 이동하기만 합니다. 실제로 활용하려면 오류 검사를 추가하거나 작업 실패 시 운영자에게 전자 메일 메시지를 보내도록 해야 할 수도 있습니다.

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

1. 개체 탐색기에서 SQL Server 에이전트를 확장하고 작업을 마우스 오른쪽 단추로 클릭한 다음 새 작업을 클릭합니다.

2. 새 작업 대화 상자의 이름 입력란에 데이터베이스 메일 보관을 입력합니다.

3. 소유자 드롭다운 목록의 해당 소유자가 sysadmin 고정 서버 역할의 멤버인지 확인합니다.

4. 범주 드롭다운 목록에서 데이터베이스 유지 관리를 클릭합니다.

5. 설명 입력란에 Archive Database Mail messages를 입력하고 단계를 클릭합니다.

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

1. 단계 페이지에서 새로 만들기를 클릭합니다.

2. 단계 이름 입력란에 데이터베이스 메일 항목 복사를 입력합니다.

3. 유형 드롭다운 목록에서 **Transact-SQL 스크립트(T-SQL)**를 선택합니다.

4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

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

   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 ;
   

6. 확인을 클릭하여 단계를 저장합니다.

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

1. 단계 페이지에서 새로 만들기를 클릭합니다.

2. 단계 이름 입력란에 데이터베이스 메일 첨부 파일 복사를 입력합니다.

3. 유형 드롭다운 목록에서 **Transact-SQL 스크립트(T-SQL)**를 선택합니다.

4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

5. 명령 입력란에 다음 문을 입력하여 이전 달의 이름을 딴 테이블을 만들고 이전 단계에서 복사한 메시지에 해당하는 첨부 파일이 포함되도록 합니다.

   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 ;
   

6. 확인을 클릭하여 단계를 저장합니다.

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

1. 단계 페이지에서 새로 만들기를 클릭합니다.

2. 단계 이름 입력란에 데이터베이스 메일 로그 복사를 입력합니다.

3. 유형 드롭다운 목록에서 **Transact-SQL 스크립트(T-SQL)**를 선택합니다.

4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

5. 명령 입력란에 다음 문을 입력하여 이전 달의 이름을 딴 테이블을 만들고 이전 단계에서 복사한 메시지에 해당하는 로그 항목이 포함되도록 합니다.

   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 ;
   

6. 확인을 클릭하여 단계를 저장합니다.

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

1. 단계 페이지에서 새로 만들기를 클릭합니다.

2. 단계 이름 입력란에 데이터베이스 메일에서 행 제거를 입력합니다.

3. 유형 드롭다운 목록에서 **Transact-SQL 스크립트(T-SQL)**를 선택합니다.

4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

5. 명령 입력란에 다음 문을 입력하여 현재 달보다 이전인 행을 데이터베이스 테이블에서 제거합니다.

   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 ;
   

6. 확인을 클릭하여 단계를 저장합니다.

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

1. 단계 페이지에서 새로 만들기를 클릭합니다.

2. 단계 이름 입력란에 데이터베이스 메일 이벤트 로그에서 행 제거를 입력합니다.

3. 유형 드롭다운 목록에서 **Transact-SQL 스크립트(T-SQL)**를 선택합니다.

4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.

5. 명령 입력란에 다음 문을 입력하여 현재 달보다 이전인 행을 데이터베이스 메일 이벤트 로그에서 제거합니다.

   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 ;
   

6. 확인을 클릭하여 단계를 저장합니다.

새로운 달이 시작될 때 실행할 작업을 예약하려면

1. 새 작업 대화 상자에서 일정을 클릭합니다.

2. 일정 페이지에서 새로 만들기를 클릭합니다.

3. 이름 입력란에 데이터베이스 메일 보관을 입력합니다.

4. 일정 유형 드롭다운 목록에서 되풀이를 선택합니다.

5. 빈도 영역에서 매월 첫 날 해당 작업을 실행하도록 옵션을 선택합니다.

6. 일별 빈도 영역에서 한 번 수행 - 오전 3:00:00을 선택합니다.

7. 다른 옵션이 원하는 대로 구성되었는지 확인하고 확인을 눌러 일정을 저장합니다.

8. 확인을 클릭하여 작업을 저장합니다.

보안

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

참고 항목

참조

데이터베이스 메일 뷰(Transact-SQL)

데이터베이스 메일 및 SQL 메일 저장 프로시저(Transact-SQL)

Creating SQL Server Agent Jobs

개념

데이터베이스 메일