Share via

  • Artykuł

How to: Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs

Kopie wiadomości pocztowych bazy danych i ich załączniki są zachowywane w msdb tabel z bazy danych korespondencji w dzienniku zdarzeń.Co pewien czas można zmniejszyć rozmiar tabel i usuwania wiadomości i zdarzeń, które nie są już potrzebne.Poniższe procedury tworzenia zadanie agenta programu SQL Server, aby zautomatyzować proces.

  1. Pierwsza procedura tworzy zadanie o nazwie archiwum poczty bazy danych z czterech krokach.

  2. Pierwszym krokiem kopiuje wszystkie wiadomości z tabel bazy danych korespondencji do nowej tabela o nazwie poprzedniego miesiąca w formacie DBMailArchive_<year_month >.

  3. Drugim krokiem kopiuje załączniki związane z skopiowany w kroku pierwszym z tabel bazy danych korespondencji do nowej tabela o nazwie poprzedniego miesiąca w formacie wiadomości DBMailArchive_Attachments_<year_month >.

  4. Trzeci krok kopiuje zdarzenie z bazy danych korespondencji w dzienniku zdarzeń związanych z skopiowany w kroku pierwszym z tabel bazy danych korespondencji do nowej tabela o nazwie poprzedniego miesiąca w formacie wiadomości DBMailArchive_Log_<year_month >.

  5. Krok czwarty usuwa rekordy z elementów przeniesionych poczty z tabel bazy danych korespondencji.

  6. Krok piąty usuwa zdarzenie związane z elementów poczty przeniesione z dziennika zdarzeń poczty bazy danych.

  7. Końcowe procedury planuje zadanie ma być uruchamiane na początku każdego miesiąca.

W tym przykładzie w tabeli archiwum są tworzone w msdb bazy danych.Aby zmniejszyć rozmiar msdb w bazie danych nowych tabel mogą znajdować się w bazie danych archiwum specjalne lub wiersze mogą zostać wyeksportowane do pliku tekstowego lub po prostu usunięte.W tym przykładzie przenosi wierszy tylko do nowych tabel w msdb bazy danych.Do użycia w produkcji można dodać sprawdzania dodatkowych błędów i wysłać wiadomość e-mail do podmiotów gospodarczych, jeśli zadanie nie powiedzie się.

Aby utworzyć zadanie agenta programu SQL Server

  1. W Eksploratorze obiektów rozwiń węzeł SQL Server Agent, kliknij prawym przyciskiem myszy Zadania, a następnie kliknij przycisk Nowe zadanie.

  2. W Nowe zadanie w dialogowym Nazwa wpisz archiwum poczty bazy danych.

  3. W Właściciel należy się upewnić, że właściciel jest członkiem sysadmin ustalić roli serwera.

  4. W Kategoria kliknijKonserwacja bazy danych.

  5. W Opis box, type Archiwizowanie wiadomości pocztowych bazy danych, a następnie kliknij przycisk Kroki.

Aby utworzyć krok do archiwizowania wiadomości pocztowych bazy danych

  1. Na Kroki strona, kliknij przycisk Nowy.

  2. W Nazwa kroku wpisz elementy poczty kopii bazy danych.

  3. W Typ Wybierz opcję poluSkrypt języka Transact-SQL (T-SQL).

  4. W Bazy danych Wybierz opcję polumsdb.

  5. W Polecenie wpisz następujące instrukcja utworzyć tabela o nazwie w poprzednim miesiącu, zawierające wiersze starsze od początku bieżącego miesiąca:

    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. Kliknij przycisk OK , aby zapisać w kroku.

Aby utworzyć krok, aby zarchiwizować załączniki poczty bazy danych

  1. Na Kroki strona, kliknij przycisk Nowy.

  2. W Nazwa kroku wpisz załączniki poczty kopii bazy danych.

  3. W Typ Wybierz opcję poluSkrypt języka Transact-SQL (T-SQL).

  4. W Bazy danych Wybierz opcję polumsdb.

  5. W Polecenie wpisz następujące instrukcja utworzyć tabela załączników o nazwie w poprzednim miesiącu, zawierające załączniki, które odpowiadają wiadomości przesyłane w poprzednim kroku:

    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. Kliknij przycisk OK , aby zapisać w kroku.

Aby utworzyć krok, aby zarchiwizować dziennik bazy danych korespondencji

  1. Na Kroki strona, kliknij przycisk Nowy.

  2. W Nazwa kroku wpisz kopii bazy danych wiadomości dziennika.

  3. W Typ Wybierz opcję poluSkrypt języka Transact-SQL (T-SQL).

  4. W Bazy danych Wybierz opcję polumsdb.

  5. W Polecenie wpisz następujące instrukcja Aby utworzyć tabela dziennika o nazwie w poprzednim miesiącu, zawierający wpisy dziennika które odpowiadają wiadomości przesyłane w kroku wcześniejszych:

    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. Kliknij przycisk OK , aby zapisać w kroku.

Aby utworzyć krok do usunięcia zarchiwizowanych wierszy z bazy danych korespondencji

  1. Na Kroki strona, kliknij przycisk Nowy.

  2. W Nazwa kroku wpisz Usuń wiersze z mail bazy danych.

  3. W Typ Wybierz opcję poluSkrypt języka Transact-SQL (T-SQL).

  4. W Bazy danych Wybierz opcję polumsdb.

  5. W Polecenie wpisz następującą instrukcję, aby usunąć wiersze starsze niż bieżący miesiąc z tabel bazy danych programu Poczta:

    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. Kliknij przycisk OK , aby zapisać w kroku.

Do tworzenia kroku usunięcie zarchiwizowanych elementów z bazy danych korespondencji zdarzenie dziennika

  1. Na Kroki strona, kliknij przycisk Nowy.

  2. W Nazwa kroku polu wpisz Usuń wiersze z bazy danych korespondencji w dzienniku zdarzeń.

  3. W Typ Wybierz opcję poluSkrypt języka Transact-SQL (T-SQL).

  4. W Bazy danych Wybierz opcję polumsdb.

  5. W Polecenie wpisz następującą instrukcję, aby usunąć wiersze starsze niż bieżący miesiąc z bazy danych pocztą zdarzenie dziennika:

    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. Kliknij przycisk OK , aby zapisać w kroku.

Aby zaplanować zadanie uruchamiane na początku każdego miesiąca

  1. W Nowe zadanie okno dialogowe kliknijHarmonogramy.

  2. Na Harmonogramy strona, kliknij przycisk Nowy.

  3. W Nazwa wpisz archiwum poczty bazy danych.

  4. W Typ harmonogramu Wybierz opcję poluCykliczne.

  5. W Częstotliwość obszaru, zaznacz odpowiednie opcje w celu uruchomienia zadanie w pierwszym dniu każdego miesiąca.

  6. W Częstotliwość dzienne Wybierz opcję obszarzeWystępuje po godzinie 3: 00: 00.

  7. Sprawdź, czy inne opcje są skonfigurowane jako rozliczona, a następnie kliknij przycisk OK zapisać harmonogramu.

  8. Kliknij przycisk OK , aby zapisać to zadanie.

Security

Musisz być członkiem sysadmin ustala rolę serwera do wykonywania procedur przechowywanych, opisane w tym temacie.