Bagikan melalui

Log transaksi

  • Artikel

Berlaku untuk: SQL Server

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. Jika ada kegagalan sistem, Anda memerlukan log tersebut untuk membawa database Anda kembali ke status yang konsisten.

Peringatan

Jangan pernah menghapus atau memindahkan log ini kecuali Anda sepenuhnya memahami ramifikasi melakukannya.

Untuk informasi tentang arsitektur log transaksi dan internal, lihat arsitektur log transaksi dan panduan manajemen SQL Server.

Tip

Poin bagus yang diketahui dari mana untuk mulai menerapkan log transaksi selama pemulihan database dibuat oleh titik pemeriksaan. Untuk informasi selengkapnya, lihat Titik pemeriksaan database (SQL Server).

Operasi yang didukung oleh log transaksi

Log transaksi mendukung operasi berikut:

  • Pemulihan transaksi individual.
  • Pemulihan semua transaksi yang tidak lengkap saat SQL Server dimulai.
  • Menggulung database, file, grup file, atau halaman yang dipulihkan ke titik kegagalan.
  • Mendukung replikasi transaksional.
  • Mendukung ketersediaan tinggi dan solusi pemulihan bencana: Grup ketersediaan AlwaysOn, pencerminan database, dan pengiriman log.

Pemulihan transaksi individual

Jika aplikasi mengeluarkan ROLLBACK pernyataan, atau jika Mesin Database mendeteksi kesalahan seperti hilangnya komunikasi dengan klien, rekaman log digunakan untuk mengembalikan modifikasi yang dibuat oleh transaksi yang tidak lengkap.

Pemulihan semua transaksi yang tidak lengkap saat SQL Server dimulai

Jika server gagal, database mungkin dibiarkan dalam keadaan di mana beberapa modifikasi tidak pernah ditulis dari cache buffer ke file data, dan mungkin ada beberapa modifikasi dari transaksi yang tidak lengkap dalam file data. Saat instans SQL Server dimulai, SQL Server menjalankan pemulihan setiap database. Setiap modifikasi yang dicatat dalam log yang mungkin belum ditulis ke file data digulung ke depan. Setiap transaksi yang tidak lengkap yang ditemukan dalam log transaksi kemudian digulung balik untuk memastikan integritas database dipertahankan. Untuk informasi selengkapnya, lihat Gambaran Umum Pemulihan dan Pemulihan (SQL Server).

Menggulung database, file, grup file, atau halaman yang dipulihkan ke titik kegagalan

Setelah kehilangan perangkat keras atau kegagalan disk yang memengaruhi file database, Anda dapat memulihkan database ke titik kegagalan. Anda terlebih dahulu memulihkan cadangan database lengkap terakhir dan cadangan database diferensial terakhir, lalu memulihkan urutan cadangan log transaksi berikutnya ke titik kegagalan.

Saat Anda memulihkan setiap cadangan log, Mesin Database menerapkan kembali semua modifikasi yang dicatat dalam log untuk meneruskan semua transaksi. Ketika cadangan log terakhir dipulihkan, Mesin Database kemudian menggunakan informasi log untuk mengembalikan semua transaksi yang tidak selesai pada saat itu. Untuk informasi selengkapnya, lihat Gambaran Umum Pemulihan dan Pemulihan (SQL Server).

Mendukung replikasi transaksional

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. Untuk informasi selengkapnya, lihat Cara Kerja Replikasi Transaksional.

Mendukung ketersediaan tinggi dan solusi pemulihan bencana

Solusi server siaga, grup ketersediaan AlwaysOn, pencerminan database, dan pengiriman log, sangat bergantung pada log transaksi.

Dalam skenario grup ketersediaan AlwaysOn, setiap pembaruan ke database pada replika utama segera direproduksi dalam salinan terpisah database pada semua replika sekunder. Replika utama mengirim setiap rekaman log segera ke replika sekunder, yang menerapkan rekaman log masuk ke database ketersediaan, terus menerus meneruskan log. Untuk informasi selengkapnya, lihat Instans kluster failover AlwaysOn (SQL Server).

Dalam skenario pengiriman log, server utama mengirimkan cadangan log transaksi database utama ke satu atau beberapa tujuan. Setiap server sekunder memulihkan cadangan log ke database sekunder lokalnya. Untuk informasi selengkapnya, lihat Tentang pengiriman log (SQL Server).

Dalam skenario pencerminan database, setiap pembaruan ke database, database utama, segera diproduksi ulang dalam salinan lengkap database yang terpisah, database cermin. Instans server utama mengirim setiap catatan log segera ke instans server cermin, yang menerapkan rekaman log masuk ke database cermin, terus menerus meneruskan. Untuk informasi selengkapnya, lihat Pencerminan Database (SQL Server).

Karakteristik log transaksi

Karakteristik log transaksi Mesin Database SQL Server:

  • Log transaksi diimplementasikan sebagai file terpisah atau sekumpulan file dalam database. Cache log dikelola secara terpisah dari cache buffer untuk halaman data, yang menghasilkan kode sederhana, cepat, dan kuat dalam Mesin Database SQL Server. Untuk informasi selengkapnya, lihat Arsitektur Fisik Log Transaksi.

  • Format rekaman log dan halaman tidak dibatasi untuk mengikuti format halaman data.

  • Log transaksi dapat diimplementasikan dalam beberapa file. File dapat didefinisikan untuk diperluas secara otomatis dengan mengatur FILEGROWTH nilai untuk log. Ini mengurangi potensi kehabisan ruang dalam log transaksi, sementara pada saat yang sama mengurangi overhead administratif. Untuk informasi selengkapnya, lihat ALTER DATABASE (Transact-SQL) File dan Opsi Grup File.

  • Mekanisme untuk menggunakan kembali ruang dalam file log cepat dan memiliki efek minimal pada throughput transaksi.

Untuk informasi tentang arsitektur log transaksi dan internal, lihat arsitektur log transaksi dan panduan manajemen SQL Server.

Pemotongan log transaksi

Pemotongan log membebaskan ruang dalam file log untuk digunakan kembali oleh log transaksi. Anda harus secara teratur memotong log transaksi Anda agar tidak mengisi ruang yang dialokasikan. Beberapa faktor dapat menunda pemotongan log, jadi pemantauan ukuran log penting. Beberapa operasi dapat dicatat minimal untuk mengurangi dampaknya pada ukuran log transaksi.

Pemotongan log menghapus file log virtual (VLF) 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 akhirnya mengisi semua ruang disk yang dialokasikan untuk file log fisik.

Untuk menghindari kehabisan ruang, kecuali pemotongan log tertunda karena alasan tertentu, pemotongan terjadi secara otomatis setelah peristiwa berikut:

  • Di bawah model pemulihan sederhana, setelah titik pemeriksaan.

  • Di bawah model pemulihan penuh atau model pemulihan yang dicatat secara massal, jika titik pemeriksaan telah terjadi sejak pencadangan sebelumnya, pemotongan terjadi setelah pencadangan log (kecuali jika itu adalah cadangan log hanya-salin).

  • Ketika Anda pertama kali membuat database menggunakan model pemulihan penuh, log transaksi digunakan kembali sesuai kebutuhan (mirip dengan database menggunakan model pemulihan sederhana), hingga saat Anda membuat cadangan database lengkap.

Untuk informasi selengkapnya, lihat Faktor-faktor yang dapat menunda pemotongan log, nanti di artikel ini.

Pemotongan log tidak mengurangi ukuran file log fisik. Untuk mengurangi ukuran fisik file log fisik, Anda harus menyusutkan file log. Untuk informasi tentang menyusutkan ukuran file log fisik, lihat Mengelola ukuran file log transaksi. Namun, perlu diingat Faktor-faktor yang dapat menunda pemotongan log. Jika ruang penyimpanan diperlukan lagi setelah log menyusut, log transaksi akan tumbuh lagi dan dengan melakukan itu, memperkenalkan overhead performa selama operasi pertumbuhan log.

Faktor-faktor yang dapat menunda pemotongan log

Ketika catatan log tetap aktif untuk waktu yang lama, pemotongan log transaksi tertunda, dan log transaksi dapat diisi, seperti yang kami sebutkan sebelumnya dalam artikel ini.

Penting

Untuk informasi tentang cara merespons log transaksi lengkap, lihat Memecahkan masalah log transaksi penuh (Kesalahan SQL Server 9002).

Sungguh, pemotongan log dapat ditunda oleh berbagai alasan. Pelajari apa, jika ada, yang mencegah pemotongan log Anda dengan mengkueri log_reuse_wait kolom dan log_reuse_wait_desc tampilan katalog sys.databases . Tabel berikut ini menjelaskan nilai kolom ini.

nilai log_reuse_wait nilai log_reuse_wait_desc Deskripsi
0 NOTHING Saat ini ada satu atau beberapa file log virtual (VLF) yang dapat digunakan kembali.
1 CHECKPOINT Tidak ada titik pemeriksaan yang terjadi sejak pemotongan log terakhir, atau kepala log belum bergerak melampaui file log virtual (VLF) (Semua model pemulihan).

Ini adalah alasan rutin untuk menunda pemotongan log. Untuk informasi selengkapnya, lihat Titik pemeriksaan database (SQL Server).
2 LOG_BACKUP Pencadangan log diperlukan sebelum log transaksi dapat dipotong. (Hanya model pemulihan penuh atau dicatat massal)

Ketika pencadangan log berikutnya selesai, beberapa ruang log mungkin dapat digunakan kembali.
3 ACTIVE_BACKUP_OR_RESTORE Pencadangan data atau pemulihan sedang berlangsung (Semua model pemulihan).

Jika cadangan data mencegah pemotongan log, membatalkan operasi pencadangan mungkin membantu masalah segera.
4 ACTIVE_TRANSACTION Transaksi aktif (Semua model pemulihan):

Transaksi yang berjalan lama mungkin ada di awal pencadangan log. Dalam hal ini, membebaskan ruang mungkin memerlukan cadangan log lain. Transaksi jangka panjang mencegah pemotongan log di bawah semua model pemulihan, termasuk model pemulihan sederhana, di mana log transaksi umumnya dipotong pada setiap titik pemeriksaan otomatis.

Transaksi ditangguhkan. Transaksi yang ditangguhkan secara efektif merupakan transaksi aktif yang pembatalannya diblokir karena beberapa sumber daya yang tidak tersedia. Untuk informasi tentang penyebab transaksi yang ditangguhkan dan cara memindahkannya keluar dari status ditangguhkan, lihat Transaksi Yang Ditangguhkan (SQL Server).

Transaksi jangka panjang mungkin juga mengisi tempdblog transaksi. tempdb digunakan secara implisit oleh transaksi pengguna untuk objek internal seperti tabel kerja untuk pengurutan, file kerja untuk hashing, tabel kerja kursor, dan penerapan versi baris. Bahkan jika transaksi pengguna hanya mencakup membaca data (SELECT kueri), objek internal mungkin dibuat dan digunakan di bawah transaksi pengguna. tempdb Kemudian log transaksi dapat diisi.
5 DATABASE_MIRRORING Pencerminan database dijeda, atau dalam mode performa tinggi, database cermin secara signifikan berada di belakang database utama. (Hanya model pemulihan penuh).

Untuk informasi selengkapnya, lihat Pencerminan Database (SQL Server).
6 REPLICATION Selama replikasi transaksional, transaksi yang relevan dengan publikasi masih belum terkirmankan ke database distribusi. (Hanya model pemulihan penuh)

Untuk informasi tentang replikasi transaksional, lihat Replikasi SQL Server.
7 DATABASE_SNAPSHOT_CREATION Rekam jepret database sedang dibuat (Semua model pemulihan).

Ini adalah rutinitas, dan biasanya singkat, penyebab pemotongan log tertunda.
8 LOG_SCAN Pemindaian log terjadi (Semua model pemulihan).

Ini adalah rutinitas, dan biasanya singkat, penyebab pemotongan log tertunda.
9 AVAILABILITY_REPLICA Replika sekunder dari grup ketersediaan menerapkan catatan log transaksi database ini ke database sekunder yang sesuai. (Hanya model pemulihan penuh).

Untuk informasi selengkapnya, lihat Apa itu grup ketersediaan AlwaysOn?.
10 - Khsus penggunaan internal
11 - Khsus penggunaan internal
12 - Khsus penggunaan internal
13 OLDEST_PAGE Jika database dikonfigurasi untuk menggunakan titik pemeriksaan tidak langsung, halaman terlama pada database mungkin lebih lama dari nomor urutan log titik pemeriksaan (LSN). Dalam hal ini, halaman terlama dapat menunda pemotongan log (Semua model pemulihan).

Untuk informasi tentang titik pemeriksaan tidak langsung, lihat Titik pemeriksaan database (SQL Server).
14 OTHER_TRANSIENT Nilai ini saat ini tidak digunakan.
16 XTP_CHECKPOINT Titik pemeriksaan OLTP Dalam Memori perlu dilakukan. Untuk tabel yang dioptimalkan memori, titik pemeriksaan otomatis diambil ketika file log transaksi menjadi lebih besar dari 1,5 GB sejak titik pemeriksaan terakhir (termasuk tabel berbasis disk dan memori yang dioptimalkan).

Untuk informasi selengkapnya, lihat Operasi Titik Pemeriksaan untuk Tabel yang Dioptimalkan Memori dan [Proses Pengelogan dan Titik Pemeriksaan untuk Tabel yang Dioptimalkan Dalam Memori] (https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/)

Operasi yang dapat dicatat secara minimal

Pengelogan minimal hanya melibatkan pengelogan informasi yang diperlukan untuk memulihkan transaksi tanpa mendukung pemulihan point-in-time. Artikel ini mengidentifikasi operasi yang dicatat secara minimal di bawah model pemulihan yang dicatat secara massal (serta di bawah model pemulihan sederhana, kecuali saat pencadangan berjalan).

Pengelogan minimal tidak didukung untuk tabel yang dioptimalkan memori.

Di bawah model pemulihan penuh, semua operasi massal dicatat sepenuhnya. Namun, Anda dapat meminimalkan pengelogan untuk sekumpulan operasi massal dengan mengalihkan database ke model pemulihan yang dicatat secara massal untuk sementara waktu untuk operasi massal. Pengelogan minimal lebih efisien daripada pengelogan penuh, dan mengurangi kemungkinan operasi massal skala besar mengisi ruang log transaksi yang tersedia selama transaksi massal. Namun, jika database rusak atau hilang saat pengelogan minimal berlaku, Anda tidak dapat memulihkan database ke titik kegagalan.

Operasi berikut, yang sepenuhnya dicatat di bawah model pemulihan penuh, dicatat minimal di bawah model pemulihan sederhana dan dicatat secara massal:

  • Operasi impor massal (bcp, BULK INSERT, dan INSERT). Untuk informasi selengkapnya tentang kapan impor massal ke tabel dicatat secara minimal, lihat Prasyarat untuk pengelogan minimal dalam impor massal.

    Ketika replikasi transaksional diaktifkan, BULK INSERT operasi sepenuhnya dicatat bahkan di bawah model pemulihan yang dicatat secara massal.

  • SELECT - OPERASI klausa INTO.

    Ketika replikasi transaksional diaktifkan, SELECT INTO operasi sepenuhnya dicatat bahkan di bawah model pemulihan yang dicatat secara massal.

  • Pembaruan parsial untuk jenis data nilai besar, menggunakan .WRITE klausa dalam pernyataan UPDATE saat menyisipkan atau menambahkan data baru. Pengelogan minimal tidak digunakan saat nilai yang ada diperbarui. Untuk informasi selengkapnya tentang jenis data bernilai besar, lihat Jenis data.

  • Pernyataan WRITETEXT dan UPDATETEXT saat menyisipkan atau menambahkan data baru ke dalam kolom tipe data teks, ntext, dan gambar . Pengelogan minimal tidak digunakan saat nilai yang ada diperbarui.

    Peringatan

    Pernyataan WRITETEXT dan UPDATETEXT tidak digunakan lagi; hindari menggunakannya di aplikasi baru.

  • Jika database diatur ke model pemulihan sederhana atau dicatat secara massal, beberapa operasi DDL indeks dicatat secara minimal apakah operasi dijalankan secara offline atau online. Operasi indeks yang dicatat minimal adalah sebagai berikut:

    • OPERASI CREATE INDEX (termasuk tampilan terindeks).

    • UBAH PEMBANGUNAN ULANG ATAU DBCC DBREINDEX operasi INDEKS.

      Operasi build indeks menggunakan pengelogan minimal, tetapi mungkin tertunda ketika ada pencadangan yang dijalankan secara bersamaan. Penundaan ini disebabkan oleh persyaratan sinkronisasi halaman kumpulan buffer yang dicatat minimal saat menggunakan model pemulihan sederhana atau dicatat massal.

      Peringatan

      Pernyataan DBCC DBREINDEX tidak digunakan lagi; hindari menggunakannya di aplikasi baru.

    • DROP INDEX membangun kembali tumpukan baru (jika berlaku). Dealokasi halaman indeks selama DROP INDEX operasi selalu dicatat sepenuhnya.

Tugas Artikel
Mengelola log transaksi - Mengelola ukuran file log transaksi

- Memecahkan masalah log transaksi penuh (Kesalahan SQL Server 9002)
Mencadangkan log transaksi (hanya model pemulihan penuh) - Mencadangkan log transaksi

- Mencadangkan Log Transaksi Saat Database Rusak (SQL Server)
Memulihkan log transaksi (hanya model pemulihan penuh) - Memulihkan Pencadangan Log Transaksi (SQL Server)

Konten terkait