데이터베이스 메일 메시지 및 이벤트 로그 보관을 처리하는 SQL Server 에이전트 작업 만들기Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs

데이터베이스 메일 및 첨부 파일의 복사본은 데이터베이스 메일 이벤트 로그와 함께 msdb 테이블에 보관됩니다.Copies of Database Mail messages and their attachments are retained in msdb tables along with the Database Mail event log. 정기적으로 테이블의 크기를 축소하고 더 이상 필요하지 않은 메시지와 이벤트를 보관할 수 있습니다.Periodically you might want to reduce the size of the tables and archive messages and events that are no longer needed. 다음 절차에서는 SQL Server 에이전트 작업을 만들어 이 프로세스를 자동화합니다.The following procedures create a SQL Server Agent job to automate the process.

시작하기 전에 Before You Begin

필수 구성 요소 Prerequisites

데이터를 저장하고 보관할 새 테이블은 특수한 보관 데이터베이스에 위치할 수 있습니다.The new tables to store the archive data might be located in a special archive database. 행을 텍스트 파일로 내보낼 수도 있습니다.Alternatively the rows could be exported to a text file.

권장 사항 Recommendations

프로덕션 환경에서 오류 검사를 추가하고 작업이 실패할 경우 운영자에게 전자 메일 메시지를 보낼 수 있습니다.In your production environment, you might want to add additional error checking and send an e-mail message to operators if the job fails.

권한 Permissions

이 항목에서 설명하는 저장 프로시저를 실행하려면 sysadmin 고정 서버 역할의 멤버여야 합니다.You must be a member of the sysadmin fixed server role to execute the stored procedures described in this topic.

프로세스 개요 Overview of the Process

  • 첫 번째 절차에서는 다음 단계를 사용하여 Archive Database Mail이라는 작업을 만듭니다.The first procedure creates a job named Archive Database Mail with the following steps.

    1. 모든 메시지를 데이터베이스 메일 테이블에서 DBMailArchive_<year_month> 형식으로 이전 달 이름에 따라 명명된 새 테이블로 복사합니다.Copy all messages from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_<year_month>.

    2. 첫 번째 단계에서 복사된 메시지와 관련된 첨부 파일을 데이터베이스 메일 테이블에서 DBMailArchive_Attachments_<year_month> 형식으로 이전 달 이름에 따라 명명된 새 테이블로 복사합니다.Copy the attachments related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Attachments_<year_month>.

    3. 첫 번째 단계에서 복사한 메시지와 관련된 데이터베이스 메일 이벤트 로그에서 DBMailArchive_Log_<year_month> 형식으로 이전 달 이름을 따라 명명된 새 테이블로 이벤트를 복사합니다.Copy the events from the Database Mail event log that are related to the messages copied in the first step, from the Database Mail tables to a new table named after the previous month in the format DBMailArchive_Log_<year_month>.

    4. 전송된 메일 항목의 레코드를 데이터베이스 메일 테이블에서 삭제합니다.Delete the records of the transferred mail items from the Database Mail tables.

    5. 전송된 메일 항목과 관련된 이벤트를 데이터베이스 메일 이벤트 로그에서 삭제합니다.Delete the events related to the transferred mail items from the Database Mail event log.

  • 정기적으로 실행되도록 작업을 예약합니다.Schedule the job to run periodically.

SQL Server 에이전트 작업을 만들려면To create a SQL Server Agent job

  1. 개체 탐색기에서 SQL ServerSQL Server 에이전트를 확장하고 작업을 마우스 오른쪽 단추로 클릭한 다음 새 작업을 클릭합니다.In Object Explorer, expand SQL ServerSQL Server Agent, right-click Jobs, and then click New Job.

  2. 새 작업 대화 상자의 이름 입력란에 Archive Database Mail을 입력합니다.In the New Job dialog box, in the Name box, type Archive Database Mail.

  3. 소유자 드롭다운 목록의 해당 소유자가 sysadmin 고정 서버 역할의 멤버인지 확인합니다.In the Owner box, confirm that the owner is a member of the sysadmin fixed server role.

  4. 범주 드롭다운 목록에서 데이터베이스 유지 관리를 클릭합니다.In the Category box, click the Database Maintenance.

  5. 설명 입력란에 Archive Database Mail messages를 입력하고 단계를 클릭합니다.In the Description box, type Archive Database Mail messages, and then click Steps.

    개요Overview

데이터베이스 메일 메시지 보관 단계를 만들려면To create a step to archive the Database Mail messages

  1. 단계 페이지에서 새로 만들기를 클릭합니다.On the Steps page, click New.

  2. 단계 이름 입력란에 Copy Database Mail Items를 입력합니다.In the Step name box, type Copy Database Mail Items.

  3. 유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.In the Type box, select Transact-SQL script (T-SQL).

  4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.In the Database box, select msdb.

  5. 명령 입력란에 다음 문을 입력하여 이전 달의 이름을 딴 테이블을 만들고 현재 달의 시작일보다 이전인 행을 포함하도록 합니다.In the Command box, type the following statement to create a table named after the previous month, containing rows older than the start of the current month:

    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. 확인 을 클릭하여 단계를 저장합니다.Click OK to save the step.

    개요Overview

데이터베이스 메일 첨부 파일 보관 단계를 만들려면To create a step to archive the Database Mail attachments

  1. 단계 페이지에서 새로 만들기를 클릭합니다.On the Steps page, click New.

  2. 단계 이름 입력란에 Copy Database Mail Attachments를 입력합니다.In the Step name box, type Copy Database Mail Attachments.

  3. 유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.In the Type box, select Transact-SQL script (T-SQL).

  4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.In the Database box, select msdb.

  5. 명령 입력란에 다음 문을 입력하여 이전 달의 이름을 딴 테이블을 만들고 이전 단계에서 복사한 메시지에 해당하는 첨부 파일이 포함되도록 합니다.In the Command box, type the following statement to create an attachments table named after the previous month, containing the attachments that correspond to the messages transferred in the previous step:

    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. 확인 을 클릭하여 단계를 저장합니다.Click OK to save the step.

    개요Overview

데이터베이스 메일 로그 보관 단계를 만들려면To create a step to archive the Database Mail log

  1. 단계 페이지에서 새로 만들기를 클릭합니다.On the Steps page, click New.

  2. 단계 이름 입력란에 Copy Database Mail Log를 입력합니다.In the Step name box, type Copy Database Mail Log.

  3. 유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.In the Type box, select Transact-SQL script (T-SQL).

  4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.In the Database box, select msdb.

  5. 명령 입력란에 다음 문을 입력하여 이전 달의 이름을 딴 테이블을 만들고 이전 단계에서 복사한 메시지에 해당하는 로그 항목이 포함되도록 합니다.In the Command box, type the following statement to create a log table named after the previous month, containing the log entries that correspond to the messages transferred in the earlier step:

    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. 확인 을 클릭하여 단계를 저장합니다.Click OK to save the step.

    개요Overview

데이터베이스 메일에서 보관된 행을 제거하는 단계를 만들려면To create a step to remove the archived rows from Database Mail

  1. 단계 페이지에서 새로 만들기를 클릭합니다.On the Steps page, click New.

  2. 단계 이름 입력란에 Remove rows from Database Mail을 입력합니다.In the Step name box, type Remove rows from Database Mail.

  3. 유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.In the Type box, select Transact-SQL script (T-SQL).

  4. 데이터베이스 드롭다운 목록에서 msdb를 선택합니다.In the Database box, select msdb.

  5. 명령 입력란에 다음 문을 입력하여 현재 달보다 이전인 행을 데이터베이스 테이블에서 제거합니다.In the Command box, type the following statement to remove rows older than the current month from the Database Mail tables:

    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. 확인 을 클릭하여 단계를 저장합니다.Click OK to save the step.

    개요Overview

데이터베이스 메일 이벤트 로그에서 보관된 항목을 제거하는 단계를 만들려면To create a step to remove the archived items from Database Mail event log

  1. 단계 페이지에서 새로 만들기를 클릭합니다.On the Steps page, click New.

  2. 단계 이름 입력란에 Remove rows from Database Mail event log를 입력합니다.In the Step Name box type Remove rows from Database Mail event log.

  3. 유형 드롭다운 목록에서 Transact-SQL 스크립트(T-SQL)를 선택합니다.In the Type box, select Transact-SQL script (T-SQL).

  4. 명령 입력란에 다음 문을 입력하여 현재 달보다 이전인 행을 데이터베이스 메일 이벤트 로그에서 제거합니다.In the Command box, type the following statement to remove rows older than the current month from the Database Mail event log:

    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 ;  
    
  5. 확인 을 클릭하여 단계를 저장합니다.Click OK to save the step.

    개요Overview

정기적으로 실행되도록 작업을 예약하려면To schedule the job to run periodically

  1. 새 작업 대화 상자에서 일정을 클릭합니다.In the New Job dialog box, click Schedules.

  2. 일정 페이지에서 새로 만들기를 클릭합니다.On the Schedules page, click New.

  3. 이름 입력란에 Archive Database Mail을 입력합니다.In the Name box, type Archive Database Mail.

  4. 일정 유형 드롭다운 목록에서 되풀이를 선택합니다.In the Schedule type box, select Recurring.

  5. 빈도 영역에서 정기적으로 작업을 실행할 옵션을 선택합니다(예: 매월 한 번).In the Frequency area, select the options to run the job periodically, for example once every month.

  6. 일별 빈도 영역에서 한 번 수행 - <시간>을(를) 선택합니다.In the Daily frequency area, select Occurs once at <time>.

  7. 다른 옵션이 원하는 대로 구성되었는지 확인하고 확인 을 눌러 일정을 저장합니다.Verify that the other options are configured as you wish, and then click OK to save the schedule.

  8. 확인 을 클릭하여 작업을 저장합니다.Click OK to save the job.

    개요Overview