Udostępnij za pośrednictwem

Tworzenie zadania agenta programu SQL Server w celu archiwizowania wiadomości e-mail i dzienników zdarzeń bazy danych

  • Artykuł

Dotyczy:programu SQL ServerAzure SQL Managed Instance

Kopie wiadomości Poczty baz danych i ich załączników są przechowywane w msdb tabelach wraz z dziennikiem zdarzeń Poczty bazy danych. Okresowo możesz zmniejszyć rozmiar tabel i archiwalnych komunikatów i zdarzeń, które nie są już potrzebne. Poniższe procedury umożliwiają utworzenie zadania agenta programu SQL Server w celu zautomatyzowania procesu.

Przed rozpoczęciem

Warunki wstępne

Nowe tabele do przechowywania danych archiwum mogą znajdować się w specjalnej bazie danych archiwum. Alternatywnie można wyeksportować wiersze do pliku tekstowego.

Zalecenia

W środowisku produkcyjnym możesz dodać dodatkowe sprawdzanie błędów i wysłać wiadomość e-mail do operatorów, jeśli zadanie zakończy się niepowodzeniem.

Uprawnienia

Aby wykonać opisane w tym temacie procedury składowane, musisz być członkiem stałej roli serwera sysadmin.

Omówienie procesu

  • Pierwsza procedura tworzy zadanie o nazwie Archive Database Mail z poniższymi krokami.

    1. Skopiuj wszystkie wiadomości z tabel Database Mail do nowej tabeli o nazwie po poprzednim miesiącu w formacie DBMailArchive_<year_month>.

    2. Skopiuj załączniki powiązane z wiadomościami skopiowanymi w pierwszym kroku z tabel Poczty w bazie danych do nowej tabeli nazwanej na podstawie poprzedniego miesiąca w formacie DBMailArchive_Attachments_<year_month>.

    3. Skopiuj zdarzenia z dziennika zdarzeń Database Mail, które są powiązane z komunikatami skopiowanymi w pierwszym kroku, z tabel Database Mail do nowej tabeli nazwanej na podstawie poprzedniego miesiąca w formacie DBMailArchive_Log_<year_month>.

    4. Usuń rekordy przeniesionych elementów poczty z tabel Poczty bazy danych.

    5. Usuń zdarzenia związane z przekazanymi elementami poczty z dziennika zdarzeń poczty bazy danych.

  • Zaplanuj, aby zadanie uruchamiało się okresowo.

Aby utworzyć zadanie agenta programu SQL Server

  1. W Eksploratorze obiektów rozwiń SQL Server Agent, kliknij prawym przyciskiem myszy pozycję Zadań, a następnie kliknij Nowe Zadanie.

  2. W oknie dialogowym Nowe zadanie w polu Nazwa wpisz Archive Database Mail.

  3. W polu właściciel upewnij się, że właściciel jest członkiem stałej roli serwera sysadmin.

  4. W polu Category kliknij Database Maintenance.

  5. W polu Opis wpisz Archiwalne wiadomości e-mail bazy danych, a następnie kliknij przycisk Kroki.

Przegląd

Aby utworzyć krok w celu zarchiwizowania wiadomości poczty bazy danych

  1. Na stronie Kroki kliknij pozycję Nowy.

  2. W polu Nazwa kroku wpisz Kopiuj elementy poczty w bazie danych.

  3. W polu typu wybierz skrypt Transact-SQL (T-SQL).

  4. W polu Database wybierz msdb.

  5. W polu polecenia wpisz następującą instrukcję, aby utworzyć tabelę o nazwie po poprzednim miesiącu zawierającą wiersze starsze niż początek 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ć krok.

Przegląd

Aby utworzyć krok w celu zarchiwizowania załączników poczty bazy danych

  1. Na stronie Kroki kliknij pozycję Nowy.

  2. W polu Nazwa kroku wpisz Kopiowanie załączników poczty bazy danych.

  3. W polu typu wybierz skrypt Transact-SQL (T-SQL).

  4. W polu Database wybierz pozycję msdb.

  5. W polecenia wpisz następującą instrukcję, aby utworzyć tabelę załączników o nazwie po poprzednim miesiącu, zawierającą załączniki odpowiadające komunikatom przesłanym 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ć krok.

— omówienie

Aby utworzyć krok w celu zarchiwizowania dziennika wysyłania wiadomości w bazie danych

  1. Na stronie Kroki kliknij pozycję Nowy.

  2. W polu Nazwa kroku wpisz Copy Database Mail Log.

  3. W polu Wpisz wybierz pozycję skrypt Transact-SQL (T-SQL).

  4. W polu Database wybierz pozycję msdb.

  5. W polu polecenia wpisz następującą instrukcję, aby utworzyć tabelę dziennika nazwaną na podstawie poprzedniego miesiąca, zawierającą wpisy dziennika, które odpowiadają komunikatom przesłanym we wcześniejszym 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_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ć krok.

Przegląd

Aby utworzyć krok usuwania zarchiwizowanych wierszy z poczty bazy danych

  1. Na stronie Kroki kliknij przycisk Nowy.

  2. W polu Nazwa kroku wpisz Usuń wiersze z Database Mail.

  3. W polu typu wybierz skrypt Transact-SQL (T-SQL).

  4. W polu Database wybierz pozycję msdb.

  5. W polu polecenia wpisz następującą instrukcję, aby usunąć wiersze starsze niż bieżący miesiąc z tabel bazy danych poczty.

    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ć krok.

Omówienie

Aby utworzyć krok usuwania zarchiwizowanych elementów z dziennika zdarzeń poczty bazy danych

  1. Na stronie Kroki kliknij pozycję Nowy.

  2. W polu Nazwa kroku wpisz Usuń wiersze z dziennika zdarzeń poczty bazy danych.

  3. W polu Wpisz wybierz pozycję skrypt Transact-SQL (T-SQL).

  4. W polu polecenia wpisz następującą instrukcję, aby usunąć wiersze starsze niż bieżący miesiąc z dziennika zdarzeń poczta bazy danych:

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

— omówienie

Aby zaplanować okresowe uruchamianie zadania

  1. W oknie dialogowym Nowe zadanie kliknij Harmonogramy .

  2. Na stronie Harmonogramy kliknij pozycję Nowy.

  3. W polu Nazwa wpisz Archive Database Mail.

  4. W polu Typ harmonogramu wybierz Cykliczne.

  5. W obszarze Częstotliwość, wybierz opcje okresowego uruchamiania zadania, na przykład raz w miesiącu.

  6. W obszarze częstotliwości dziennej wybierz opcję Występuje raz o <godzinie>.

  7. Sprawdź, czy inne opcje są skonfigurowane zgodnie z życzeniem, a następnie kliknij przycisk OK, aby zapisać harmonogram.

  8. Kliknij przycisk OK, aby zapisać zadanie.

Przegląd