pengembangan-web-mp-pd.com

Bagaimana cara mengurangi ukuran cadangan log transaksi setelah cadangan penuh?

Saya memiliki tiga rencana pemeliharaan yang diatur untuk dijalankan pada instance Sql Server 2005:

  • Optimasi basis data mingguan diikuti dengan cadangan penuh
  • Pencadangan diferensial harian
  • Pencadangan log transaksi harian

Pencadangan log per jam biasanya antara beberapa ratus Kb dan 10Mb tergantung pada tingkat aktivitas, perbedaan harian biasanya tumbuh sekitar 250Mb pada akhir minggu, dan cadangan mingguan sekitar 3,5 Gb.

Masalah yang saya miliki adalah bahwa optimisasi sebelum cadangan penuh tampaknya menyebabkan cadangan log transaksi berikutnya tumbuh lebih dari 2x ukuran cadangan penuh, dalam hal ini 8Gb, sebelum kembali normal.

Selain daripada BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY, apakah ada cara untuk mengurangi ukuran cadangan log itu, atau mencegah optimisasi dicatat dalam log transaksi sama sekali, karena pasti mereka akan diperhitungkan dalam cadangan penuh yang mereka lakukan sebelumnya?

38
Dave

Beberapa saran menarik di sini, yang semuanya tampaknya menunjukkan kesalahpahaman tentang cara kerja cadangan log. Cadangan log berisi SEMUA log transaksi yang dibuat sejak cadangan log sebelumnya, apa pun cadangan lengkap atau diferensial yang diambil untuk sementara. Menghentikan pencadangan log atau pindah ke pencadangan penuh harian tidak akan berpengaruh pada ukuran cadangan log. Satu-satunya hal yang mempengaruhi log transaksi adalah cadangan log, setelah rantai cadangan log dimulai.

Satu-satunya pengecualian untuk aturan ini adalah jika rantai cadangan log telah rusak (mis. Dengan masuk ke model pemulihan SIMPLE, kembali dari snapshot database, memotong log menggunakan LOG CADANGAN DENGAN NO_LOG/TRUNCATE_ONLY), dalam hal ini cadangan log pertama akan berisi semua log transaksi sejak cadangan penuh terakhir - yang memulai kembali rantai cadangan log; atau jika rantai cadangan log belum dimulai - ketika Anda beralih ke FULL untuk pertama kalinya, Anda beroperasi dalam semacam model pemulihan pseudo-SIMPLE sampai cadangan penuh pertama diambil.

Untuk menjawab pertanyaan awal Anda, tanpa masuk ke model pemulihan SEDERHANA, Anda harus menyedot cadangan semua log transaksi. Bergantung pada tindakan yang Anda lakukan, Anda dapat mengambil lebih banyak cadangan log untuk mengurangi ukurannya, atau melakukan lebih banyak basis data yang ditargetkan.

Jika Anda dapat memposting beberapa info tentang operasi pemeliharaan yang Anda lakukan, saya dapat membantu Anda mengoptimalkannya. Apakah Anda, secara kebetulan, melakukan pembangunan kembali indeks yang diikuti oleh basis data yang menyusut untuk merebut kembali ruang yang digunakan oleh indeks yang dibangun kembali?

Jika Anda tidak memiliki aktivitas lain dalam database saat pemeliharaan sedang terjadi, Anda dapat melakukan hal berikut:

  • pastikan aktivitas pengguna dihentikan
  • mengambil cadangan log akhir (ini memungkinkan Anda untuk memulihkan hingga titik pemeliharaan dimulai)
    • beralih ke model pemulihan SEDERHANA
    • melakukan pemeliharaan - log akan terpotong di setiap pos pemeriksaan
    • beralih ke model pemulihan LENGKAP dan mengambil cadangan penuh
    • terus seperti biasa

Semoga ini bisa membantu - menantikan info lebih lanjut.

Terima kasih

[Sunting: setelah semua diskusi tentang apakah cadangan lengkap dapat mengubah ukuran cadangan log berikutnya (tidak bisa) saya mengumpulkan posting blog yang komprehensif dengan bahan latar belakang dan skrip yang membuktikannya. Lihat di https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]

35
Paul Randal

Anda dapat mengecilkan mereka, tetapi mereka hanya akan tumbuh lagi, akhirnya menyebabkan fragmentasi disk. Pembuatan ulang indeks dan defrag membuat log transaksi yang sangat besar. Jika Anda tidak memerlukan pemulihan point-in-time, Anda dapat mengubah ke mode pemulihan sederhana dan menghapus sepenuhnya cadangan log transaksi.

Saya menduga Anda menggunakan rencana pemeliharaan untuk pengoptimalan, Anda dapat mengubahnya menggunakan skrip yang melakukan pengindeksan defrag hanya ketika tingkat fragmentasi tertentu tercapai dan Anda kemungkinan tidak akan mengalami peningkatan kinerja. Ini akan menghasilkan log yang jauh lebih kecil.

Saya akan melewatkan perbedaan harian demi cadangan lengkap harian BTW.

5
SqlACID

Pertanyaan terakhir Anda adalah: "Selain LOG BACKUP DENGAN TRUNCATE_ONLY, apakah ada cara untuk mengurangi ukuran cadangan log itu, atau mencegah optimisasi dicatat dalam log transaksi sama sekali, karena pasti mereka akan diperhitungkan karena dalam cadangan penuh, mereka mendahului? "

Tidak, tapi ini solusinya. Jika Anda tahu bahwa satu-satunya kegiatan dalam database itu pada saat itu adalah pekerjaan pemeliharaan indeks, maka Anda dapat menghentikan pencadangan log transaksi sebelum pemeliharaan indeks dimulai. Misalnya, beberapa server saya pada Sabtu malam, jadwal pekerjaan terlihat seperti ini:

  • 9:30 PM - pencadangan log transaksi berjalan.
  • 9:45 PM - pencadangan log transaksi berjalan untuk yang terakhir kalinya. Jadwal berhenti pada 9:59.
  • 10:00 PM - pekerjaan pemeliharaan indeks dimulai dan memiliki penghentian bawaan untuk selesai sebelum 11:30.
  • 11:30 PM - pekerjaan cadangan penuh dimulai dan selesai dalam waktu kurang dari 30 menit.
  • 12:00 - pencadangan log transaksi dimulai lagi setiap 15 menit.

Itu berarti saya tidak memiliki pemulihan point-in-time antara 9:45 dan 11:30 malam, tetapi hasilnya adalah kinerja yang lebih cepat.

3
Brent Ozar

Jawaban mudah: Ubah pekerjaan pengoptimalan mingguan Anda agar berjalan lebih seimbang setiap malam. mis. indeks ulang tabel a-e pada Minggu malam, f - l pada Senin malam dll ... temukan keseimbangan yang baik, log Anda akan kira-kira 1/6 dari ukuran rata-rata. Tentu saja ini bekerja paling baik jika Anda tidak menggunakan pekerjaan pemeliharaan indeks ssis bawaan.

Kelemahan dari ini dan itu signifikan tergantung pada beban pengalaman db Anda adalah bahwa hal itu mendatangkan malapetaka dengan optimizer dan penggunaan kembali rencana permintaan.

Tetapi jika yang Anda pedulikan adalah ukuran t-log Anda setiap minggu, bagilah dari hari ke hari atau jam ke jam dan jalankan cadangan t-log di antaranya.

3
Jeremy Lowell

Anda mungkin juga melihat ke alat pihak ketiga (Litespeed dari Quest, SQL Backup dari Red Gate, Hyperbac) untuk mengurangi ukuran cadangan dan log. Mereka dapat membayar sendiri dengan cepat dalam penghematan kaset.

2
Steve Jones

Dapatkah Anda secara khusus membuat cadangan log transaksi Anda di berbagai titik selama optimasi database Anda? Ukuran total t-log akan sama, tetapi masing-masing akan lebih kecil, mungkin membantu Anda dalam beberapa cara.

Bisakah Anda melakukan optimasi basis data yang lebih bertarget sehingga lebih sedikit transaksi yang dibuat (seseorang menyebutkan ini, tetapi saya tidak yakin implikasinya dijabarkan). Seperti mentolerir sejumlah fragmentasi atau ruang kosong untuk sementara waktu. Jika 40% dari tabel Anda hanya 5% terfragmentasi, tidak menyentuhnya bisa menghemat sedikit aktivitas.

2
ErikE

Mungkin dapat diasumsikan bahwa "optimisasi" Anda termasuk pembangunan kembali indeks. Hanya melakukan tugas-tugas ini setiap minggu yang dapat diterima pada database yang tidak mengalami banyak pembaruan dan sisipan, namun jika data Anda sangat lancar, Anda mungkin ingin melakukan beberapa hal:

  1. Buat kembali atau atur ulang indeks Anda setiap malam jika jadwal Anda memungkinkan dan jika dampaknya dapat diterima. Saat melakukan tugas pemeliharaan indeks malam ini, targetkan hanya indeks yang terfragmentasi melebihi 30% untuk pembangunan kembali dan dalam kisaran 15-30% untuk perbaikan.

  2. Tugas-tugas ini adalah transaksi log, jadi jika Anda khawatir tentang pertumbuhan log maka saya akan menganjurkan apa yang direkomendasikan Paul. Pencadangan log transaksi akhir sebelum pemeliharaan indeks, beralih ke Pemulihan sederhana, diikuti oleh proses pemeliharaan dan kemudian kembali ke Pemulihan penuh diikuti oleh pencadangan data lengkap harus melakukan trik.

Saya mengambil pendekatan seperti zen untuk file log saya: mereka adalah ukuran yang mereka inginkan. Selama mereka tidak mengalami pertumbuhan yang menyolok karena praktik pencadangan yang buruk dibandingkan dengan aktivitas basis data yang merupakan mantra yang saya jalani.

Adapun skrip yang melakukan pemeliharaan indeks pilihan terlihat online: ada satu ton di luar sana. Andrew Kelly menerbitkan yang layak di SQL Magazine sekitar setahun yang lalu. SQLServerPedia memiliki beberapa skrip dari Michelle Ufford, dan edisi terbaru SQL Magazine (Juli 2009 saya percaya) memiliki artikel lengkap tentang topik ini juga. Intinya adalah menemukan satu yang bekerja dengan baik untuk Anda dan menjadikannya milik Anda dengan kustomisasi minimal.

2
Tim Ford