Arsitektur dan panduan manajemen log transaksi SQL Server

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform 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 status yang konsisten. Panduan ini menyediakan informasi tentang arsitektur fisik dan logis 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 catatan log baru ditulis ke akhir log yang logis dengan LSN yang lebih tinggi dari LSN rekaman sebelumnya. Rekaman log disimpan dalam urutan serial saat dibuat, sehingga jika LSN2 lebih besar dari LSN1, perubahan yang dijelaskan oleh catatan log yang disebutkan 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 pemutaran kembali transaksi.

Struktur dasar LSN adalah [VLF ID:Log Block ID:Log Record ID]. Untuk informasi selengkapnya, lihat bagian VLF dan blok log.

Berikut adalah contoh LSN: 00000031:00000da0:0001, di mana 0x31 adalah ID VLF, 0xda0 adalah ID blok log, dan 0x1 merupakan catatan log pertama di blok log tersebut. Untuk contoh LSN, lihat output sys.dm_db_log_info DMV dan periksa vlf_create_lsn kolom .

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 logis dicatat

  • Untuk meneruskan operasi logis, operasi dilakukan lagi.
  • Untuk mengembalikan operasi logis, operasi logis terbalik dilakukan.

• Sebelum dan sesudah gambar dicatat

  • Untuk meneruskan operasi, gambar setelah diterapkan.
  • Untuk mengembalikan 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). Modifikasi termasuk perubahan berdasarkan prosedur tersimpan sistem atau pernyataan bahasa definisi data (DDL) ke tabel apa pun, termasuk tabel sistem.

• Setiap tingkat dan alokasi halaman atau dealokasi.

• Membuat atau menghilangkan tabel atau indeks.

Operasi pembatalan juga dicatat. Setiap transaksi mencadangkan ruang dalam log transaksi untuk memastikan bahwa ada cukup ruang log untuk mendukung pembatalan yang disebabkan oleh pernyataan pembatalan eksplisit, atau jika terjadi kesalahan. Jumlah ruang yang dicadangkan 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 untuk pembatalan seluruh database yang berhasil ke rekaman log terakhir 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 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 beberapa 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 beberapa 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.

Pembuatan file log virtual

Pembuatan file log virtual (VLF) mengikuti metode ini:

• Di SQL Server 2014 (12.x) dan versi yang lebih baru, jika pertumbuhan berikutnya kurang dari 1/8 dari ukuran fisik log saat ini, buat 1 VLF yang mencakup ukuran pertumbuhan.
• Jika pertumbuhan berikutnya lebih dari 1/8 dari ukuran log saat ini, gunakan metode pra-2014, yaitu:
  • Jika pertumbuhan kurang dari 64 MB, buat 4 VLF yang mencakup ukuran pertumbuhan (misalnya, untuk pertumbuhan 1-MB, buat 4 VLF berukuran 256 KB).
    • Di Azure SQL Database, dan dimulai dengan SQL Server 2022 (16.x) (semua edisi), logikanya sedikit berbeda. Jika pertumbuhan kurang dari atau sama dengan 64 MB, Mesin Database hanya membuat satu VLF untuk mencakup ukuran pertumbuhan.
  • Jika pertumbuhan dari 64 MB hingga 1 GB, buat 8 VLF yang mencakup ukuran pertumbuhan (misalnya, untuk pertumbuhan 512 MB, buat 8 VLF ukuran 64 MB).
  • Jika pertumbuhan lebih besar dari 1 GB, buat 16 VLF yang mencakup ukuran pertumbuhan misalnya, untuk pertumbuhan 8 GB, buat 16 VLF ukuran 512 MB).

Jika file log tumbuh ke ukuran besar dalam banyak kenaikan kecil, file log virtual berakhir dengan banyak file log virtual. Ini dapat memperlambat startup database, mencatat operasi pencadangan dan pemulihan, dan menyebabkan replikasi transaksional/CDC dan latensi pengulangan AlwaysOn. Sebaliknya, jika file log diatur ke ukuran besar dengan beberapa atau hanya satu kenaikan, file log virtual berisi 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 Mengelola ukuran file log transaksi.

Kami menyarankan agar Anda membuat file log mendekati ukuran akhir yang diperlukan, menggunakan kenaikan yang diperlukan untuk mencapai distribusi VLF yang optimal, dan memiliki nilai growth_increment yang relatif besar.

Lihat tips berikut untuk menentukan distribusi VLF optimal untuk ukuran log transaksi saat ini:

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

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

Apa yang terjadi ketika Anda memiliki terlalu banyak VLF?

Selama tahap awal proses pemulihan database, SQL Server menemukan semua VLF di semua file log transaksi, dan membangun daftar VLF ini. Proses ini dapat memakan waktu lama tergantung pada jumlah VLF yang ada dalam database tertentu. Semakin banyak VLF, semakin lama prosesnya. Database dapat berakhir dengan sejumlah besar VLF jika sering kali pertumbuhan otomatis log transaksi atau pertumbuhan manual ditemui dalam kenaikan kecil. Ketika jumlah VLF mencapai kisaran beberapa ratus ribu, Anda mungkin mengalami beberapa atau sebagian besar gejala berikut:

• Satu atau beberapa database membutuhkan waktu yang sangat lama untuk menyelesaikan pemulihan selama startup SQL Server.
• Memulihkan database membutuhkan waktu yang sangat lama untuk diselesaikan.
• Upaya untuk melampirkan database membutuhkan waktu yang sangat lama untuk diselesaikan.
• Ketika Anda mencoba menyiapkan pencerminan database, Anda mengalami pesan kesalahan 1413, 1443, dan 1479, menunjukkan batas waktu.
• Anda mengalami kesalahan terkait memori seperti 701 saat mencoba memulihkan database.
• Replikasi transaksional atau tangkapan data perubahan mungkin mengalami latensi yang signifikan.

Saat Anda memeriksa log Kesalahan SQL Server, Anda mungkin melihat bahwa sejumlah besar waktu yang dihabiskan sebelum fase analisis proses pemulihan database. Contohnya:

2022-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2022-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

Selain itu, SQL Server dapat mencatat kesalahan 9017 saat Anda memulihkan database yang memiliki sejumlah besar VLF:

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Untuk informasi selengkapnya, lihat MSSQLSERVER_9017.

Memperbaiki database dengan sejumlah besar VLF

Untuk menjaga jumlah total VLF pada jumlah yang wajar, seperti maksimum beberapa ribu, Anda dapat mengatur ulang file log transaksi agar berisi jumlah VLF yang lebih kecil dengan melakukan langkah-langkah berikut:

1. Susutkan file log transaksi secara manual.

2. Kembangkan file ke ukuran yang diperlukan secara manual dalam satu langkah menggunakan skrip T-SQL berikut:

   ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

   Catatan

   Langkah ini juga dimungkinkan di SQL Server Management Studio, menggunakan halaman properti database.

Setelah Anda mengatur tata letak baru file log transaksi dengan lebih sedikit VLF, tinjau dan buat perubahan yang diperlukan pada pengaturan pertumbuhan otomatis log transaksi. Validasi pengaturan ini memastikan bahwa file log menghindari mengalami masalah yang sama di masa mendatang.

Sebelum Anda melakukan salah satu operasi ini, pastikan Anda memiliki cadangan yang valid jika Anda mengalami masalah nanti.

Untuk menentukan distribusi VLF optimal untuk ukuran log transaksi saat ini dari semua database dalam instans tertentu, dan kenaikan pertumbuhan yang diperlukan untuk mencapai ukuran yang diperlukan, Anda dapat menggunakan skrip GitHub berikut untuk memperbaiki VLF.

Blok log

Setiap VLF berisi satu atau beberapa blok log. Setiap blok log terdiri dari rekaman log (diselaraskan pada batas 4 byte). Blok log berukuran variabel dan selalu merupakan kelipatan bilangan bulat 512 byte (ukuran sektor minimum yang didukung SQL Server), dengan ukuran maksimum 60 KB. Blok log adalah unit dasar I/O untuk pengelogan transaksi.

Singkatnya, blok log adalah kontainer rekaman log yang digunakan sebagai unit dasar pengelogan transaksi saat menulis rekaman log ke disk.

Setiap blok log dalam VLF ditangani secara unik oleh offset bloknya. Blok pertama selalu memiliki offset blok yang menunjuk melewati 8 KB pertama di VLF.

Secara umum, VLF selalu diisi dengan blok log. Ada kemungkinan bahwa blok log terakhir dalam VLF kosong (misalnya, tidak berisi catatan log apa pun). Ini terjadi ketika catatan log yang akan ditulis tidak masuk ke blok log saat ini dan juga ketika ruang yang tersisa di VLF tidak cukup untuk menyimpan rekaman log ini. Dalam hal ini, blok log kosong dibuat yang mengisi VLF. Catatan log disisipkan ke blok pertama pada VLF berikutnya.

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 log 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 catatannya semuanya muncul di depan nomor urutan log pemulihan minimum (MinLSN). MinLSN adalah nomor urutan log dari catatan log tertua 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 logika mencapai akhir file log fisik, catatan log baru membungkus ke awal file log fisik.

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

• FILEGROWTH Jika pengaturan diaktifkan untuk log dan ruang tersedia di disk, file diperluas dengan 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 memegang file log memiliki lebih sedikit ruang kosong 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 log logis berpindah melalui semua file log fisik sebelum membungkus kembali ke awal file log fisik pertama.

Pemotongan log

Pemotongan log sangat penting untuk menjaga log tidak terisi. Pemotongan log menghapus file log virtual yang tidak aktif dari log transaksi logis database SQL Server, membebaskan ruang di 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 untuk 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, pemotongan log dapat membebaskan bagian yang tidak aktif. 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 dipotong. 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 berada 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 belum 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 dibeberkan untuk digunakan kembali. Log logis sekarang dimulai pada awal log virtual 3. Log virtual 5 masih belum 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 (Kesalahan SQL Server 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 (juga disebut kumpulan buffer) di mana ia membaca halaman data ketika data harus diambil. Ketika halaman dimodifikasi di cache buffer, halaman tidak segera ditulis kembali ke disk; sebagai gantinya, 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, catatan log transaksi disisipkan dalam cache log yang merekam modifikasi. Rekaman 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 cache buffer ke disk disebut membersihkan halaman. SQL Server memiliki logika yang mencegah halaman kotor dihapus sebelum catatan 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 dapat membuat cadangan log pertama, Anda harus membuat cadangan penuh, seperti pencadangan database atau yang pertama dalam satu set cadangan file. Memulihkan database dengan hanya menggunakan cadangan file yang dapat menjadi kompleks. Oleh karena itu, kami sarankan Anda memulai dengan pencadangan database lengkap ketika 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.

Untuk membatasi jumlah cadangan log yang perlu Anda pulihkan, penting untuk mencadangkan data Anda secara rutin. Misalnya, Anda mungkin menjadwalkan pencadangan database penuh mingguan dan cadangan database diferensial harian.

Pikirkan tentang RTO dan RPO yang diperlukan saat menerapkan strategi pemulihan Anda, dan khususnya irama cadangan database lengkap dan diferensial.

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

Rantai log

Urutan pencadangan 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 cadangan log berikutnya melalui titik pemulihan Anda. Titik pemulihan bisa menjadi akhir 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 ter putus 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 serangkaian cadangan file lengkap. Untuk informasi selengkapnya, lihat Menerapkan Pencadangan Log Transaksi (SQL Server).

Memulihkan cadangan 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 pencadangan file pertama yang Anda pulihkan. Biasanya, setelah Anda memulihkan data terbaru atau cadangan diferensial, Anda harus memulihkan serangkaian cadangan log sampai 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 log

Titik pemeriksaan menghapus halaman data kotor dari singgahan 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 digulung 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.

  Salah satu informasi yang direkam di titik pemeriksaan adalah nomor urutan log (LSN) dari catatan 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 dari awal transaksi aktif tertua.
  • LSN dari awal transaksi replikasi terlama yang belum dikirimkan ke database distribusi.

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

• Jika database menggunakan model pemulihan sederhana, tanda 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 penyalinan massal dilakukan pada database yang menggunakan model pemulihan Yang Dicatat Massal.
• 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. Ini dapat terjadi ketika opsi AUTO_CLOSE aktif dan koneksi pengguna terakhir ke database ditutup. Contoh lain adalah ketika 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 rekaman 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 rekaman log mencapai angka yang diperkirakan 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 yang dapat diproses mesin database selama waktu yang ditentukan dalam opsi interval pemulihan.

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

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 dipotong 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 catatan log terakhir ditulis disebut bagian aktif dari log, atau log aktif. Ini adalah bagian dari log yang diperlukan untuk melakukan pemulihan penuh database. Tidak ada bagian dari log aktif yang dapat dipotong. 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 catatan log terlama untuk transaksi aktif pada saat titik pemeriksaan terakhir. Ini membuat LSN 142, Catatan transaksi Mulai untuk Tran 2, MinLSN.

Transaksi jangka panjang

Log aktif harus mencakup setiap bagian dari semua transaksi yang tidak dijalankan. Aplikasi yang memulai transaksi dan tidak menerapkannya atau mengembalikannya mencegah Mesin Database memajukan MinLSN. Situasi 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 dipotong pada setiap titik pemeriksaan otomatis.

Pemulihan transaksi jangka panjang, dan masalah yang dijelaskan dalam artikel ini, dapat dihindari dengan menggunakan pemulihan database Dipercepat, fitur yang tersedia dimulai dengan SQL Server 2019 (15.x) dan di Azure SQL Database.

Transaksi replikasi

Agen Pembaca Log memantau log transaksi 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 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 (T-SQL)
• sys.dm_db_log_space_usage (T-SQL)
• Memahami Pengelogan dan Pemulihan di SQL Server oleh Paul Randal
• Manajemen Log Transaksi SQL Server oleh Tony Davis dan Gail Shaw