Bagikan melalui

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

Berlaku untuk: SQL ServerAzure SQL Managed Instance

Salinan pesan Email Database dan lampirannya disimpan dalam msdb tabel 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.

Prasyarat

Untuk menjalankan perintah T-SQL pada instans SQL Server Anda, gunakan SQL Server Management Studio (SSMS), ekstensi MSSQL untuk Visual Studio Code, sqlcmd, atau alat kueri T-SQL favorit Anda.

Rekomendasi

Pertimbangkan pemeriksaan kesalahan dan pantau pekerjaan ini untuk mengirim pesan email ke operator jika pekerjaan arsip ini gagal.

Secara opsional, Anda dapat memindahkan data Email Database yang diarsipkan ke database arsip kustom di luar msdb, atau mengekspornya dari SQL Server.

Izin

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

Membuat pekerjaan Email Database Arsip

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 Basis Data ke tabel baru yang dinamai sesuai nama bulan sebelumnya dalam format DBMailArchive_Attachments_<year_month>.

  3. Salin peristiwa dari log peristiwa Database Mail yang terkait dengan pesan yang disalin di langkah pertama, dari tabel Database Mail ke tabel baru yang dinamai sesuai 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.

  6. Jadwalkan pekerjaan untuk berjalan secara berkala.

Membuat pekerjaan SQL Server Agent

Langkah-langkah berikut menggunakan SQL Server Management Studio (SSMS). Unduh versi terbaru SSMS di aka.ms/ssms.

  1. Sambungkan ke instans SQL Server.

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

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

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

  5. Dalam kotak Kategori , pilih Pemeliharaan Database.

  6. Dalam kotak Deskripsi , ketik pesan Surat Database Arsip, lalu pilih Langkah-langkah.

Buat langkah pekerjaan untuk mengarsipkan pesan Database Mail

  1. Pada halaman Langkah-langkah , pilih 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 T-SQL berikut untuk membuat tabel yang dinamai sesuai bulan sebelumnya, yang berisi baris yang lebih tua dari awal bulan 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. Pilih OK untuk menyimpan langkah.

Membuat langkah pekerjaan untuk mengarsipkan lampiran Email Database

  1. Pada halaman Langkah-langkah , pilih 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. Pilih OK untuk menyimpan langkah.

Membuat langkah pekerjaan untuk mengarsipkan log Email Database

  1. Pada halaman Langkah-langkah , pilih 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. Pilih OK untuk menyimpan langkah.

Membuat langkah pekerjaan untuk menghapus baris yang diarsipkan dari Email Database

  1. Pada halaman Langkah-langkah , pilih Baru.

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

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

Membuat langkah pekerjaan untuk menghapus item yang diarsipkan dari log peristiwa Email Database

  1. Pada halaman Langkah-langkah , pilih 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. Pilih OK untuk menyimpan langkah.

Jadwalkan pekerjaan untuk dijalankan secara berkala

  1. Dalam kotak dialog Pekerjaan Baru , pilih Jadwal.

  2. Pada halaman Jadwal , pilih Baru.

  3. Dalam kotak Nama, ketik Mail Basis Data 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 sekali pada waktu<.

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

  8. Pilih OK untuk menyimpan pekerjaan.

Konten terkait

  • Last updated on