SQL Server Arsitektur dan Panduan Manajemen Log Transaksi

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Setiap database SQL Server memiliki log transaksi yang merekam semua transaksi dan modifikasi database yang dilakukan oleh setiap transaksi. Log transaksi adalah komponen penting dari database dan, jika ada kegagalan sistem, log transaksi mungkin diperlukan untuk membawa database Anda kembali ke keadaan yang konsisten. Panduan ini menyediakan informasi tentang arsitektur fisik dan logis dari log transaksi. Memahami arsitektur dapat meningkatkan efektivitas Anda dalam mengelola log transaksi.

Arsitektur Logis Logis Transaksi

Log transaksi SQL Server beroperasi secara logis seolah-olah log transaksi adalah string rekaman log. Setiap rekaman log diidentifikasi oleh nomor urutan log (LSN). Setiap rekaman log baru ditulis ke akhir log yang logis dengan LSN yang lebih tinggi dari LSN rekaman sebelumnya. Catatan log disimpan dalam urutan serial saat dibuat sededingin itu sehingga jika LSN2 lebih besar dari LSN1, perubahan yang dijelaskan oleh catatan log yang dirujuk oleh LSN2 terjadi setelah perubahan yang dijelaskan oleh catatan log LSN1. Setiap rekaman log berisi ID transaksi miliknya. Untuk setiap transaksi, semua catatan log yang terkait dengan transaksi secara individual ditautkan dalam rantai menggunakan pointer mundur yang mempercepat putar kembali transaksi.

Catatan log untuk modifikasi data merekam operasi logis yang dilakukan atau mereka merekam gambar sebelum dan sesudah data yang dimodifikasi. Gambar sebelumnya adalah salinan data sebelum operasi dilakukan; gambar setelah adalah salinan data setelah operasi dilakukan.

Langkah-langkah untuk memulihkan operasi bergantung pada jenis catatan log:

• Operasi logika dicatat

  • Untuk menggulung operasi logis ke depan, operasi dilakukan lagi.

  • Untuk menggulung kembali operasi logis, operasi logis terbalik dilakukan.

• Sebelum dan sesudah gambar dicatat

  • Untuk menggulung operasi ke depan, gambar setelah diterapkan.

  • Untuk menggulung balik operasi, gambar sebelum diterapkan.

Banyak jenis operasi dicatat dalam log transaksi. Operasi ini meliputi:

• Awal dan akhir setiap transaksi.

• Setiap modifikasi data (sisipkan, perbarui, atau hapus). Ini termasuk perubahan oleh prosedur tersimpan sistem atau pernyataan bahasa definisi data (DDL) ke tabel apa pun, termasuk tabel sistem.

• Setiap jangkauan dan alokasi halaman atau dealokasi.

• Membuat atau menghilangkan tabel atau indeks.

Operasi pembatalan juga dicatat. Setiap transaksi mencadangkan ruang pada log transaksi untuk memastikan bahwa ada cukup ruang log untuk mendukung pembatalan yang disebabkan oleh pernyataan putar kembali eksplisit atau jika terjadi kesalahan. Jumlah ruang yang dipesan tergantung pada operasi yang dilakukan dalam transaksi, tetapi umumnya sama dengan jumlah ruang yang digunakan untuk mencatat setiap operasi. Ruang yang dipesan ini dikosongkan ketika transaksi selesai.

Bagian file log dari catatan log pertama yang harus ada agar pembatalan seluruh database berhasil ke rekaman log terakhir yang ditulis disebut bagian aktif dari log, log aktif, atau ekor log. Ini adalah bagian dari log yang diperlukan untuk pemulihan penuh database. Tidak ada bagian dari log aktif yang dapat dipotong. Nomor urutan log (LSN) dari catatan log pertama ini dikenal sebagai LSN pemulihan minimum (MinLSN). Untuk informasi selengkapnya tentang operasi yang didukung oleh log transaksi, lihat Log Transaksi (SQL Server).

Pencadangan diferensial dan log memajukan database yang dipulihkan ke lain waktu, yang sesuai dengan LSN yang lebih tinggi.

Arsitektur Fisik Log Transaksi

Log transaksi dalam database memetakan satu atau beberapa file fisik. Secara konseptual, file log adalah string rekaman log. Secara fisik, urutan rekaman log disimpan secara efisien dalam kumpulan file fisik yang mengimplementasikan log transaksi. Setidaknya harus ada satu file log untuk setiap database.

File Log Virtual (VLF)

Mesin Database SQL Server membagi setiap file log fisik secara internal menjadi sejumlah file log virtual (VLF). File log virtual tidak memiliki ukuran tetap, dan tidak ada jumlah file log virtual tetap untuk file log fisik. Mesin Database memilih ukuran file log virtual secara dinamis saat membuat atau memperluas file log. Mesin Database mencoba mempertahankan sejumlah kecil file virtual. Ukuran file virtual setelah file log diperluas adalah jumlah ukuran log yang ada dan ukuran kenaikan file baru. Ukuran atau jumlah file log virtual tidak dapat dikonfigurasi atau diatur oleh administrator.

Catatan

Pembuatan file log virtual (VLF) mengikuti metode ini:

• Jika pertumbuhan berikutnya kurang dari 1/8 dari ukuran fisik log saat ini, maka buat 1 VLF yang mencakup ukuran pertumbuhan. (Dimulai dengan SQL Server 2014 (12.x)).
• Jika pertumbuhan berikutnya lebih dari 1/8 dari ukuran log saat ini, maka gunakan metode pra-2014:
  • Jika pertumbuhan kurang dari 64MB, buat 4 VLF yang mencakup ukuran pertumbuhan (misalnya untuk pertumbuhan 1 MB, buat empat VLF 256KB).
    • Dalam Azure SQL Database dan mulai pratinjau SQL Server 2022 (16.x), ini sedikit berbeda. Jika pertumbuhannya kurang dari atau sama dengan 64MB, buat hanya satu VLF untuk menutupi ukuran pertumbuhan.
  • Jika pertumbuhan dari 64MB hingga 1GB, buat 8 VLF yang mencakup ukuran pertumbuhan (misalnya untuk pertumbuhan 512 MB, buat delapan VLF 64MB).
  • Jika pertumbuhan lebih besar dari 1GB, buat 16 VLF yang mencakup ukuran pertumbuhan (misalnya untuk pertumbuhan 8 GB, buat enam belas VLF 512MB).

Jika file log tumbuh ke ukuran besar dalam banyak kenaikan kecil, mereka akan memiliki banyak file log virtual. Ini dapat memperlambat startup database dan juga mencatat operasi pencadangan dan pemulihan. Sebaliknya, jika file log diatur ke ukuran besar dengan beberapa atau hanya satu kenaikan, mereka akan memiliki beberapa file log virtual yang sangat besar. Untuk informasi selengkapnya tentang memperkirakan ukuran dan pengaturan autogrow log transaksi yang diperlukan dengan benar, lihat bagian Rekomendasi dari Kelola ukuran file log transaksi.

Sebaiknya tetapkan file log nilai ukuran yang mendekati ukuran akhir yang diperlukan, menggunakan kenaikan yang diperlukan untuk mencapai distribusi VLF yang optimal, dan juga memiliki nilai growth_increment yang relatif besar. Lihat tips di bawah ini untuk menentukan distribusi VLF optimal untuk ukuran log transaksi saat ini.

• Nilai ukuran , sebagaimana diatur oleh SIZE argumen ALTER DATABASE adalah ukuran awal untuk file log.
• Nilai growth_increment (juga disebut sebagai nilai autogrow), sebagaimana diatur oleh FILEGROWTH argumen ALTER DATABASE, adalah jumlah ruang yang ditambahkan ke file setiap kali ruang baru diperlukan.

Untuk informasi selengkapnya tentang FILEGROWTH argumen ALTER DATABASEdan SIZE , lihat Opsi UBAH File DATABASE (Transact-SQL) dan Grup File.

Tip

Untuk menentukan distribusi VLF yang optimal untuk ukuran log transaksi saat ini dari semua database dalam instans tertentu, dan kenaikan pertumbuhan yang diperlukan untuk mencapai ukuran yang diperlukan, lihat skrip ini.

Sifat melingkar dari log transaksi

Log transaksi adalah file wrap-around. Misalnya, pertimbangkan database dengan satu file log fisik yang dibagi menjadi empat VLF. Saat database dibuat, file logis dimulai pada awal file log fisik. Rekaman log baru ditambahkan di akhir log logika dan diperluas ke akhir log fisik. Pemotongan log membebaskan log virtual apa pun yang semua rekamannya muncul di depan nomor urutan log pemulihan minimum (MinLSN). MinLSN adalah nomor urutan log dari rekaman log terlama yang diperlukan untuk pemutaran kembali di seluruh database yang berhasil. Log transaksi dalam database contoh akan terlihat mirip dengan yang ada dalam ilustrasi berikut.

Ketika akhir log logis mencapai akhir file log fisik, rekaman log baru dibungkus ke awal file log fisik.

Siklus ini berulang tanpa henti, selama akhir log logis tidak pernah mencapai awal log logis. Jika catatan log lama cukup sering dipotong untuk selalu meninggalkan ruang yang cukup untuk semua rekaman log baru yang dibuat melalui titik pemeriksaan berikutnya, log tidak pernah terisi. Namun, jika akhir logika memang mencapai awal log logis, salah satu dari dua hal terjadi:

• FILEGROWTH Jika pengaturan diaktifkan untuk log dan ruang tersedia di disk, file diperluas oleh jumlah yang ditentukan dalam parameter growth_increment dan rekaman log baru ditambahkan ke ekstensi. Untuk informasi selengkapnya tentang pengaturan, FILEGROWTH lihat MENGUBAH File DATABASE dan Opsi Grup File (Transact-SQL).

• FILEGROWTH Jika pengaturan tidak diaktifkan, atau disk yang menyimpan file log memiliki ruang kosong yang lebih sedikit daripada jumlah yang ditentukan dalam growth_increment, kesalahan 9002 dihasilkan. Lihat Memecahkan Masalah Log Transaksi Lengkap untuk informasi selengkapnya.

Jika log berisi beberapa file log fisik, log logis akan berpindah melalui semua file log fisik sebelum dibungkus kembali ke awal file log fisik pertama.

Penting

Untuk informasi selengkapnya tentang manajemen ukuran log transaksi, lihat Mengelola Ukuran File Log Transaksi.

Pemotongan Log

Pemotongan log sangat penting untuk mencegah pengisian log. Pemotongan log menghapus file log virtual yang tidak aktif dari log transaksi logis database SQL Server, membebaskan ruang dalam log logis untuk digunakan kembali oleh log transaksi fisik. Jika log transaksi tidak pernah terpotong, log tersebut pada akhirnya akan mengisi semua ruang disk yang dialokasikan ke file log fisiknya. Namun, sebelum log dapat dipotong, operasi titik pemeriksaan harus terjadi. Titik pemeriksaan menulis halaman yang dimodifikasi dalam memori saat ini (dikenal sebagai halaman kotor) dan informasi log transaksi dari memori ke disk. Ketika titik pemeriksaan dilakukan, bagian tidak aktif dari log transaksi ditandai sebagai dapat digunakan kembali. Setelah itu, bagian yang tidak aktif dapat dibebaskan dengan pemotongan log. Untuk informasi selengkapnya tentang titik pemeriksaan, lihat Titik Pemeriksaan Database (SQL Server).

Ilustrasi berikut menunjukkan log transaksi sebelum dan sesudah pemotongan. Ilustrasi pertama menunjukkan log transaksi yang belum pernah terpotong. Saat ini, empat file log virtual sedang digunakan oleh log logis. Log logis dimulai di bagian depan file log virtual pertama dan berakhir di log virtual 4. Catatan MinLSN ada di log virtual 3. Log virtual 1 dan log virtual 2 hanya berisi rekaman log yang tidak aktif. Rekaman ini dapat dipotong. Log virtual 5 masih tidak digunakan dan bukan bagian dari log logis saat ini.

Ilustrasi kedua menunjukkan bagaimana log muncul setelah dipotong. Log virtual 1 dan log virtual 2 telah dikosongkan untuk digunakan kembali. Log logis sekarang dimulai pada awal log virtual 3. Log virtual 5 masih tidak digunakan, dan bukan bagian dari log logis saat ini.

Pemotongan log terjadi secara otomatis setelah peristiwa berikut, kecuali ketika tertunda karena beberapa alasan:

• Di bawah model pemulihan sederhana, setelah titik pemeriksaan.
• Di bawah model pemulihan penuh atau model pemulihan yang dicatat secara massal, setelah pencadangan log, jika titik pemeriksaan telah terjadi sejak pencadangan sebelumnya.

Pemotongan log dapat ditunda oleh berbagai faktor. Jika terjadi penundaan panjang dalam pemotongan log, log transaksi dapat terisi. Untuk informasi, lihat Faktor-faktor yang dapat menunda pemotongan log dan Memecahkan Masalah Log Transaksi Penuh (SQL Server Kesalahan 9002).

Log Transaksi Write-Ahead

Bagian ini menjelaskan peran log transaksi write-ahead dalam merekam modifikasi data ke disk. SQL Server menggunakan algoritma write-ahead logging (WAL), yang menjamin bahwa tidak ada modifikasi data yang ditulis ke disk sebelum rekaman log terkait ditulis ke disk. Ini mempertahankan properti ACID untuk transaksi.

Untuk memahami cara kerja log write-ahead, penting bagi Anda untuk mengetahui bagaimana data yang dimodifikasi ditulis ke disk. SQL Server mempertahankan cache buffer tempatnya membaca halaman data saat data harus diambil. Ketika halaman dimodifikasi dalam cache buffer, halaman tidak segera ditulis kembali ke disk; sebaliknya, halaman ditandai sebagai kotor. Halaman data dapat memiliki lebih dari satu tulisan logis yang dibuat sebelum ditulis secara fisik ke disk. Untuk setiap penulisan logis, rekaman log transaksi disisipkan dalam cache log yang merekam modifikasi. Catatan log harus ditulis ke disk sebelum halaman kotor terkait dihapus dari cache buffer dan ditulis ke disk. Proses titik pemeriksaan secara berkala memindai cache buffer untuk buffer dengan halaman dari database tertentu dan menulis semua halaman kotor ke disk. Titik pemeriksaan menghemat waktu selama pemulihan nanti dengan membuat titik di mana semua halaman kotor dijamin telah ditulis ke disk.

Menulis halaman data yang dimodifikasi dari buffer cache ke disk disebut flushing halaman. SQL Server memiliki logika yang mencegah halaman kotor dihapus sebelum rekaman log terkait ditulis. Rekaman log ditulis ke disk ketika buffer log dihapus. Ini terjadi setiap kali transaksi dilakukan atau buffer log menjadi penuh.

Pencadangan Log Transaksi

Bagian ini menyajikan konsep tentang cara mencadangkan dan memulihkan (menerapkan) log transaksi. Di bawah model pemulihan penuh dan dicatat secara massal, mengambil cadangan rutin log transaksi (cadangan log) diperlukan untuk memulihkan data. Anda dapat mencadangkan log saat pencadangan penuh sedang berjalan. Untuk informasi selengkapnya tentang model pemulihan, lihat Mencadangkan dan Memulihkan Database SQL Server.

Sebelum Anda dapat membuat cadangan log pertama, Anda harus membuat cadangan penuh, seperti cadangan database atau yang pertama dalam sekumpulan cadangan file. Memulihkan database dengan hanya menggunakan cadangan file yang bisa menjadi kompleks. Oleh karena itu, kami sarankan Anda memulai dengan pencadangan database lengkap kapan Anda bisa. Setelah itu, mencadangkan log transaksi secara teratur diperlukan. Ini tidak hanya meminimalkan paparan kehilangan kerja tetapi juga memungkinkan pemotongan log transaksi. Biasanya, log transaksi dipotong setelah setiap pencadangan log konvensional.

Penting

Sebaiknya ambil cadangan log yang cukup sering untuk mendukung persyaratan bisnis Anda, khususnya toleransi Anda untuk kehilangan pekerjaan seperti yang mungkin disebabkan oleh penyimpanan log yang rusak. Frekuensi yang sesuai untuk mengambil cadangan log tergantung pada toleransi Anda untuk paparan kehilangan kerja yang seimbang dengan berapa banyak cadangan log yang dapat Anda simpan, kelola, dan, berpotensi, pulihkan. Pikirkan tentang RTO dan RPO yang diperlukan saat menerapkan strategi pemulihan Anda, dan khususnya irama cadangan log. Mengambil cadangan log setiap 15 hingga 30 menit mungkin cukup. Jika bisnis Anda mengharuskan Anda meminimalkan paparan kehilangan kerja, pertimbangkan untuk lebih sering mengambil cadangan log. Pencadangan log yang lebih sering memiliki keuntungan tambahan untuk meningkatkan frekuensi pemotongan log, menghasilkan file log yang lebih kecil.

Penting

Untuk membatasi jumlah cadangan log yang perlu Anda pulihkan, penting untuk mencadangkan data Anda secara rutin. Misalnya, Anda dapat menjadwalkan pencadangan database penuh mingguan dan pencadangan database diferensial harian.
Sekali lagi, pikirkan tentang RTO dan RPO yang diperlukan saat menerapkan strategi pemulihan Anda, dan khususnya irama cadangan database penuh dan diferensial.

Untuk informasi selengkapnya tentang pencadangan log transaksi, lihat Pencadangan Log Transaksi (SQL Server).

Rantai Log

Urutan cadangan log berkelanjutan disebut rantai log. Rantai log dimulai dengan pencadangan penuh database. Biasanya, rantai log baru hanya dimulai ketika database dicadangkan untuk pertama kalinya atau setelah model pemulihan dialihkan dari pemulihan sederhana ke pemulihan penuh atau dicatat secara massal. Kecuali Anda memilih untuk menimpa kumpulan cadangan yang ada saat membuat cadangan database lengkap, rantai log yang ada tetap utuh. Dengan rantai log utuh, Anda dapat memulihkan database Anda dari cadangan database lengkap apa pun di set media, diikuti oleh semua pencadangan log berikutnya melalui titik pemulihan Anda. Titik pemulihan bisa menjadi akhir dari pencadangan log terakhir atau titik pemulihan tertentu di salah satu cadangan log. Untuk informasi selengkapnya, lihat Pencadangan Log Transaksi (SQL Server).

Untuk memulihkan database hingga titik kegagalan, rantai log harus utuh. Artinya, urutan cadangan log transaksi yang tidak terurai harus diperpanjang hingga titik kegagalan. Di mana urutan log ini harus dimulai tergantung pada jenis cadangan data yang Anda memulihkan: database, parsial, atau file. Untuk database atau cadangan parsial, urutan cadangan log harus diperluas dari akhir database atau cadangan parsial. Untuk sekumpulan cadangan file, urutan cadangan log harus diperluas dari awal kumpulan cadangan file lengkap. Untuk informasi selengkapnya, lihat Menerapkan Pencadangan Log Transaksi (SQL Server).

Pulihkan Pencadangan Log

Memulihkan cadangan log akan meneruskan perubahan yang dicatat dalam log transaksi untuk membuat ulang status database yang tepat pada saat operasi pencadangan log dimulai. Saat memulihkan database, Anda harus memulihkan cadangan log yang dibuat setelah pencadangan database lengkap yang Anda pulihkan, atau dari awal cadangan file pertama yang Anda pulihkan. Biasanya, setelah memulihkan data terbaru atau cadangan diferensial, Anda harus memulihkan serangkaian cadangan log hingga Anda mencapai titik pemulihan Anda. Kemudian, Anda memulihkan database. Ini mengembalikan semua transaksi yang tidak lengkap ketika pemulihan dimulai dan membawa database online. Setelah database dipulihkan, Anda tidak dapat memulihkan cadangan lagi. Untuk informasi selengkapnya, lihat Menerapkan Pencadangan Log Transaksi (SQL Server).

Titik Pemeriksaan dan Bagian Aktif dari Log

Titik pemeriksaan menghapus halaman data kotor dari cache buffer database saat ini ke disk. Ini meminimalkan bagian aktif log yang harus diproses selama pemulihan penuh database. Selama pemulihan penuh, jenis tindakan berikut dilakukan:

• Catatan log modifikasi tidak dihapus ke disk sebelum sistem dihentikan digulirkan ke depan.
• Semua modifikasi yang terkait dengan transaksi yang tidak lengkap, seperti transaksi yang tidak ada catatan log COMMIT atau ROLLBACK, digulung balik.

Operasi Titik Pemeriksaan

Titik pemeriksaan melakukan proses berikut dalam database:

• Menulis rekaman ke file log, menandai awal titik pemeriksaan.

• Menyimpan informasi yang direkam untuk titik pemeriksaan dalam rantai rekaman log titik pemeriksaan.

  Satu bagian informasi yang dicatat dalam titik pemeriksaan adalah nomor urutan log (LSN) dari rekaman log pertama yang harus ada untuk pembatalan seluruh database yang berhasil. LSN ini disebut Minimum Recovery LSN (MinLSN). MinLSN adalah minimum dari:

  • LSN dari awal titik pemeriksaan.
  • LSN awal transaksi aktif tertua.
  • LSN awal transaksi replikasi tertua yang belum dikirimkan ke database distribusi.

  Rekaman titik pemeriksaan juga berisi daftar semua transaksi aktif yang telah memodifikasi database.

• Jika database menggunakan model pemulihan sederhana, tandai untuk menggunakan kembali ruang yang mendahului MinLSN.

• Menulis semua halaman log dan data kotor ke disk.

• Menulis rekaman yang menandai akhir titik pemeriksaan ke file log.

• Menulis LSN dari awal rantai ini ke halaman boot database.

Aktivitas yang menyebabkan Titik Pemeriksaan

Titik pemeriksaan terjadi dalam situasi berikut:

• Pernyataan CHECKPOINT dijalankan secara eksplisit. Titik pemeriksaan terjadi di database saat ini untuk koneksi.
• Operasi yang dicatat minimal dilakukan dalam database; misalnya, operasi salin massal dilakukan pada database yang menggunakan model pemulihan Bulk-Logged.
• File database telah ditambahkan atau dihapus dengan menggunakan ALTER DATABASE.
• Instans SQL Server dihentikan oleh pernyataan SHUTDOWN atau dengan menghentikan layanan SQL Server (MSSQLSERVER). Salah satu tindakan menyebabkan titik pemeriksaan di setiap database dalam instans SQL Server.
• Instans SQL Server secara berkala menghasilkan titik pemeriksaan otomatis di setiap database untuk mengurangi waktu yang diperlukan instans untuk memulihkan database.
• Pencadangan database diambil.
• Aktivitas yang mengharuskan penonaktifan database dilakukan. Misalnya, AUTO_CLOSE AKTIF dan koneksi pengguna terakhir ke database ditutup, atau perubahan opsi database dilakukan yang memerlukan mulai ulang database.

Titik Pemeriksaan Otomatis

Mesin database SQL Server menghasilkan titik pemeriksaan otomatis. Interval antara titik pemeriksaan otomatis didasarkan pada jumlah ruang log yang digunakan dan waktu berlalu sejak titik pemeriksaan terakhir. Interval waktu antara titik pemeriksaan otomatis bisa sangat bervariasi dan panjang, jika beberapa modifikasi dilakukan dalam database. Titik pemeriksaan otomatis juga dapat sering terjadi jika banyak data dimodifikasi.

Gunakan opsi konfigurasi server interval pemulihan untuk menghitung interval antara titik pemeriksaan otomatis untuk semua database pada instans server. Opsi ini menentukan waktu maksimum yang harus digunakan Mesin Database untuk memulihkan database selama menghidupkan ulang sistem. Mesin Database memperkirakan berapa banyak catatan log yang dapat diproses dalam interval pemulihan selama operasi pemulihan.

Interval antara titik pemeriksaan otomatis juga tergantung pada model pemulihan:

• Jika database menggunakan model pemulihan penuh atau dicatat secara massal, titik pemeriksaan otomatis dihasilkan setiap kali jumlah catatan log mencapai angka yang dapat diproses mesin database selama waktu yang ditentukan dalam opsi interval pemulihan.

• Jika database menggunakan model pemulihan sederhana, titik pemeriksaan otomatis dihasilkan setiap kali jumlah rekaman log mencapai lebih sedikit dari dua nilai ini:

  • Log menjadi 70 persen penuh.
  • Jumlah rekaman log mencapai angka perkiraan Mesin Database yang dapat diproses selama waktu yang ditentukan dalam opsi interval pemulihan.

Untuk informasi tentang mengatur interval pemulihan, lihat Mengonfigurasi Opsi Konfigurasi Server interval pemulihan.

Tip

Opsi penyiapan tingkat lanjut -k SQL Server memungkinkan administrator database untuk membatasi perilaku I/O titik pemeriksaan berdasarkan throughput subsistem I/O untuk beberapa jenis titik pemeriksaan. Opsi penyiapan -k berlaku untuk titik pemeriksaan otomatis dan titik pemeriksaan yang tidak dibatasi.

Titik pemeriksaan otomatis memotong bagian log transaksi yang tidak digunakan jika database menggunakan model pemulihan sederhana. Namun, jika database menggunakan model pemulihan penuh atau dicatat secara massal, log tidak dipotok oleh titik pemeriksaan otomatis. Untuk informasi selengkapnya, lihat Log Transaksi.

Pernyataan CHECKPOINT sekarang menyediakan argumen checkpoint_duration opsional yang menentukan periode waktu yang diminta, dalam hitungan detik, agar titik pemeriksaan selesai. Untuk informasi selengkapnya, lihat CHECKPOINT.

Log Aktif

Bagian file log dari MinLSN ke rekaman log terakhir ditulis disebut bagian aktif log, atau log aktif. Ini adalah bagian dari log yang diperlukan untuk melakukan pemulihan penuh database. Tidak ada bagian dari log aktif yang dapat dipotok. Semua rekaman log harus dipotong dari bagian log sebelum MinLSN.

Ilustrasi berikut menunjukkan versi yang disederhanakan dari log akhir transaksi dengan dua transaksi aktif. Rekaman titik pemeriksaan telah dikompresi ke satu rekaman.

LSN 148 adalah catatan terakhir dalam log transaksi. Pada saat titik pemeriksaan yang dicatat di LSN 147 diproses, Tran 1 telah dilakukan dan Tran 2 adalah satu-satunya transaksi aktif. Itu membuat catatan log pertama untuk Tran 2 menjadi catatan log terlama untuk transaksi yang aktif pada saat titik pemeriksaan terakhir. Ini membuat LSN 142, catatan transaksi Begin untuk Tran 2, MinLSN.

Transaksi jangka panjang

Log aktif harus mencakup setiap bagian dari semua transaksi yang tidak diterapkan. Aplikasi yang memulai transaksi dan tidak menerapkannya atau mengembalikannya mencegah Mesin Database memajukan MinLSN. Hal ini dapat menyebabkan dua jenis masalah:

• Jika sistem dimatikan setelah transaksi melakukan banyak modifikasi yang tidak dilakukan, fase pemulihan mulai ulang berikutnya dapat memakan waktu lebih lama dari waktu yang ditentukan dalam opsi interval pemulihan .
• Log mungkin tumbuh sangat besar, karena log tidak dapat dipotong melewati MinLSN. Ini terjadi bahkan jika database menggunakan model pemulihan sederhana, di mana log transaksi umumnya dipotok pada setiap titik pemeriksaan otomatis.

Dimulai dengan SQL Server 2019 (15.x) dan dalam Azure SQL Database, pemulihan transaksi jangka panjang dan masalah yang dijelaskan di atas dapat dihindari dengan menggunakan pemulihan database Dipercepat.

Transaksi replikasi

Agen Pembaca Log memantau log transaksi dari setiap database yang dikonfigurasi untuk replikasi transaksional, dan menyalin transaksi yang ditandai untuk replikasi dari log transaksi ke database distribusi. Log aktif harus berisi semua transaksi yang ditandai untuk replikasi, tetapi yang belum dikirimkan ke database distribusi. Jika transaksi ini tidak direplikasi tepat waktu, transaksi ini dapat mencegah pemotongan log. Untuk informasi selengkapnya, lihat Replikasi Transaksional.

Langkah berikutnya

Kami merekomendasikan artikel dan buku berikut untuk informasi tambahan tentang log transaksi dan praktik terbaik manajemen log.

• Log Transaksi (SQL Server)
• Mengelola ukuran file log transaksi
• Pencadangan Log Transaksi (SQL Server)
• Titik Pemeriksaan Database (SQL Server)
• Mengonfigurasi Opsi Konfigurasi Server interval pemulihan
• Pemulihan database dipercepat
• sys.dm_db_log_info (SQL Bertransaksi)
• sys.dm_db_log_space_usage (SQL Bertransaksi)
• Memahami Pengelogan dan Pemulihan di SQL Server oleh Paul Randal
• SQL Server Manajemen Log Transaksi oleh Tony Davis dan Gail Shaw