Bagikan melalui

Membuat Pekerjaan Agen SQL Server untuk Mengarsipkan Pesan Email Database dan Log Peristiwa

  • Artikel

Berlaku untuk:SQL ServerAzure SQL Managed Instance

Salinan pesan Email Database dan lampirannya disimpan dalam tabel msdb bersama dengan log peristiwa Email Database. Secara berkala Anda mungkin ingin mengurangi ukuran tabel dan mengarsipkan pesan dan peristiwa yang tidak lagi diperlukan. Prosedur berikut membuat pekerjaan SQL Server Agent untuk mengotomatiskan proses.

  • Sebelum Memulai: , Prasyarat, Rekomendasi, Izin

  • Untuk Mengarsipkan pesan dan log Email Database menggunakan :SQL Server Agent

Sebelum Anda mulai

Prasyarat

Tabel baru untuk menyimpan data arsip mungkin terletak di database arsip khusus. Atau baris dapat diekspor ke file teks.

Rekomendasi

Di lingkungan produksi, Anda mungkin ingin menambahkan pemeriksaan kesalahan tambahan dan mengirim pesan email ke operator jika pekerjaan gagal.

Izin

Anda harus menjadi anggota peran server tetap sysadmin untuk menjalankan prosedur tersimpan yang dijelaskan dalam topik ini.

Gambaran Umum Proses

  • Prosedur pertama membuat pekerjaan bernama Archive Database Mail dengan langkah-langkah berikut.

    1. Salin semua pesan dari tabel Email Database ke tabel baru bernama setelah bulan sebelumnya dalam format DBMailArchive_<year_month.>

    2. Salin lampiran yang terkait dengan pesan yang disalin di langkah pertama, dari tabel Email Database ke tabel baru bernama setelah bulan sebelumnya dalam format DBMailArchive_Attachments_<year_month.>

    3. Salin peristiwa dari log peristiwa Email Database yang terkait dengan pesan yang disalin di langkah pertama, dari tabel Email Database ke tabel baru bernama setelah bulan sebelumnya dalam format DBMailArchive_Log_<year_month.>

    4. Hapus rekaman item email yang ditransfer dari tabel Email Database.

    5. Hapus peristiwa yang terkait dengan item email yang ditransfer dari log peristiwa Email Database.

  • Jadwalkan pekerjaan untuk berjalan secara berkala.

Untuk membuat pekerjaan SQL Server Agent

  1. Di Object Explorer, perluas SQL Server Agent, klik kanan Pekerjaan, lalu klik Pekerjaan Baru.

  2. Dalam kotak dialog Pekerjaan Baru, dalam kotak Nama , ketik Arsipkan Email Database.

  3. Dalam kotak Pemilik , konfirmasikan bahwa pemilik adalah anggota peran server tetap sysadmin .

  4. Dalam kotak Kategori , klik Pemeliharaan Database.

  5. Dalam kotak Deskripsi , ketik pesan Surat Database Arsip, lalu klik Langkah-langkah.

Gambaran Umum

Untuk membuat langkah untuk mengarsipkan pesan Email Database

  1. Pada halaman Langkah-langkah , klik Baru.

  2. Dalam kotak Nama langkah, ketik Salin Item Email Database.

  3. Dalam kotak Jenis , pilih skrip Transact-SQL (T-SQL).

  4. Dalam kotak Database , pilih msdb.

  5. Dalam kotak Perintah , ketik pernyataan berikut untuk membuat tabel bernama setelah bulan sebelumnya, yang berisi baris yang lebih lama dari awal bulan saat ini:

    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. Klik OK untuk menyimpan langkah.

Gambaran Umum

Untuk membuat langkah untuk mengarsipkan lampiran Email Database

  1. Pada halaman Langkah-langkah , klik Baru.

  2. Dalam kotak Nama langkah, ketik Salin Lampiran Email Database.

  3. Dalam kotak Jenis , pilih skrip Transact-SQL (T-SQL).

  4. Dalam kotak Database , pilih msdb.

  5. Dalam kotak Perintah , ketik pernyataan berikut untuk membuat tabel lampiran bernama setelah bulan sebelumnya, yang berisi lampiran yang sesuai dengan pesan yang ditransfer di langkah sebelumnya:

    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. Klik OK untuk menyimpan langkah.

Gambaran Umum

Untuk membuat langkah untuk mengarsipkan log Email Database

  1. Pada halaman Langkah-langkah , klik Baru.

  2. Dalam kotak Nama langkah, ketik Salin Log Email Database.

  3. Dalam kotak Jenis , pilih skrip Transact-SQL (T-SQL).

  4. Dalam kotak Database , pilih msdb.

  5. Dalam kotak Perintah , ketik pernyataan berikut untuk membuat tabel log bernama setelah bulan sebelumnya, yang berisi entri log yang sesuai dengan pesan yang ditransfer di langkah sebelumnya:

    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. Klik OK untuk menyimpan langkah.

Gambaran Umum

Untuk membuat langkah untuk menghapus baris yang diarsipkan dari Email Database

  1. Pada halaman Langkah-langkah , klik Baru.

  2. Dalam kotak Nama langkah, ketik Hapus baris dari Email Database.

  3. Dalam kotak Jenis , pilih skrip Transact-SQL (T-SQL).

  4. Dalam kotak Database , pilih msdb.

  5. Dalam kotak Perintah , ketik pernyataan berikut untuk menghapus baris yang lebih lama dari bulan ini dari tabel Email Database:

    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. Klik OK untuk menyimpan langkah.

Gambaran Umum

Untuk membuat langkah untuk menghapus item yang diarsipkan dari log peristiwa Email Database

  1. Pada halaman Langkah-langkah , klik Baru.

  2. Dalam kotak Nama Langkah ketik Hapus baris dari log peristiwa Email Database.

  3. Dalam kotak Jenis , pilih skrip Transact-SQL (T-SQL).

  4. Dalam kotak Perintah , ketik pernyataan berikut untuk menghapus baris yang lebih lama dari bulan ini dari log peristiwa Email Database:

    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. Klik OK untuk menyimpan langkah.

Gambaran Umum

Untuk menjadwalkan pekerjaan agar berjalan secara berkala

  1. Dalam kotak dialog Pekerjaan Baru, klik Jadwal.

  2. Pada halaman Jadwal , klik Baru.

  3. Dalam kotak Nama , ketik Email Database Arsip.

  4. Dalam kotak Jenis jadwal, pilih Berulang.

  5. Di area Frekuensi, pilih opsi untuk menjalankan pekerjaan secara berkala, misalnya sekali setiap bulan.

  6. Di area Frekuensi harian, pilih Terjadi sekaligus><.

  7. Verifikasi bahwa opsi lain dikonfigurasi sesuai keinginan Anda, lalu klik OK untuk menyimpan jadwal.

  8. Klik OK untuk menyimpan pekerjaan.

Gambaran Umum