Log Transaksi (SQL Server)

Artikel
01/04/2017

Setiap database SQL Server memiliki log transaksi yang merekam semua transaksi dan modifikasi database yang dilakukan oleh setiap transaksi. Log transaksi harus dipotong secara teratur agar tidak terisi. Namun, beberapa faktor dapat menunda pemotongan log, sehingga pemantauan ukuran log penting. Beberapa operasi dapat dicatat minimal untuk mengurangi dampaknya pada ukuran log 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. Log transaksi tidak boleh dihapus atau dipindahkan kecuali Anda sepenuhnya memahami konsekuensi dari melakukan ini.

Catatan

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).

Dalam Topik ini:

Manfaat: Operasi yang Didukung oleh Log Transaksi
Pemotongan Log Transaksi
Faktor-faktor yang Dapat Menunda Pemotongan Log
Operasi yang Dapat Dicatat Minimal
Tugas Terkait

Manfaat: Operasi yang Didukung oleh Log Transaksi

Log transaksi mendukung operasi berikut:

Pemulihan transaksi individu.
Pemulihan semua transaksi yang tidak lengkap ketika 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.

Pemotongan Log Transaksi

Pemotongan log membebaskan ruang dalam file log untuk digunakan kembali oleh log transaksi. 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.

Untuk menghindari masalah ini, 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 itu adalah cadangan log hanya salin).

Untuk informasi selengkapnya, lihat Faktor yang Dapat Menunda Pemotongan Log, nanti dalam topik ini.

Catatan

Pemotongan log tidak mengurangi ukuran file log fisik. Untuk mengurangi ukuran fisik file log fisik, Anda perlu menyusutkan file log. Untuk informasi tentang menyusutkan ukuran file log fisik, lihat Mengelola Ukuran File Log Transaksi.

Faktor-faktor yang Dapat Menunda Pemotongan Log

Ketika catatan log tetap aktif untuk pemotongan log transaksi dalam waktu lama tertunda, dan berpotensi log transaksi dapat terisi.

Penting

Untuk informasi tentang cara merespons log transaksi lengkap, lihat Memecahkan Masalah Log Transaksi Penuh (SQL Server Kesalahan 9002).

Pemotongan log dapat ditunda oleh berbagai faktor. Anda dapat menemukan apa, jika ada, mencegah pemotongan log dengan mengkueri kolom log_reuse_wait 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	APA	Saat ini ada satu atau beberapa file log virtual yang dapat digunakan kembali.
1	TITIK PEMERIKSAAN	Tidak ada titik pemeriksaan yang terjadi sejak pemotongan log terakhir, atau kepala log belum bergerak melampaui file log virtual. (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 pencadangan 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, mengosongkan ruang mungkin memerlukan pencadangan log lain. Perhatikan bahwa transaksi yang berjalan lama mencegah pemotongan log di bawah semua model pemulihan, termasuk model pemulihan sederhana, di mana log transaksi umumnya terpotong pada setiap titik pemeriksaan otomatis. Transaksi ditangguhkan. Transaksi yang ditangguhkan secara efektif adalah 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 Tangguhan (SQL Server). Transaksi jangka panjang mungkin juga mengisi log transaksi tempdb. 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 menyertakan data baca (kueri SELECT), objek internal dapat dibuat dan digunakan di bawah transaksi pengguna. Kemudian log transaksi tempdb 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	REPLIKASI	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 sedang 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. (Model pemulihan penuh) Untuk informasi selengkapnya, lihat Gambaran Umum Grup Ketersediaan AlwaysOn (SQL Server).
10	-	Hanya untuk penggunaan internal
11	-	Hanya untuk penggunaan internal
12	-	Hanya untuk penggunaan internal
13	OLDEST_PAGE	Jika database dikonfigurasi untuk menggunakan titik pemeriksaan tidak langsung, halaman terlama pada database mungkin lebih lama dari LSN titik pemeriksaan. Dalam hal ini, halaman terlama dapat menunda pemotokan 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	Ketika database memiliki grup file yang dioptimalkan memori, log transaksi mungkin tidak terpotok hingga titik pemeriksaan OLTP In-Memory otomatis dipicu (yang terjadi pada setiap pertumbuhan log 512 MB). Catatan: Untuk memotong log transaksi sebelum ukuran 512 MB, aktifkan perintah Titik Pemeriksaan secara manual terhadap database yang dimaksud.

Operasi yang Dapat Dicatat Minimal

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

Catatan

Pengelogan minimal tidak didukung untuk tabel yang dioptimalkan memori.

Catatan

Di bawah model pemulihan penuh, semua operasi massal dicatat sepenuhnya. Namun, Anda dapat meminimalkan pengelogan untuk serangkaian 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 ketika 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... PILIH). Untuk informasi selengkapnya tentang kapan impor massal ke tabel dicatat minimal, lihat Prasyarat untuk Pengelogan Minimal dalam Impor Massal.

Catatan

Ketika replikasi transaksional diaktifkan, operasi BULK INSERT sepenuhnya dicatat bahkan di bawah model pemulihan Bulk Logged.
OPERASI SELECT INTO .

Catatan

Ketika replikasi transaksional diaktifkan, operasi SELECT INTO sepenuhnya dicatat bahkan di bawah model pemulihan Yang Dicatat Secara Massal.
Pembaruan parsial untuk jenis data nilai besar, menggunakan . Klausa WRITE dalam pernyataan UPDATE saat menyisipkan atau menambahkan data baru. Perhatikan bahwa pengelogan minimal tidak digunakan saat nilai yang ada diperbarui. Untuk informasi selengkapnya tentang jenis data nilai besar, lihat Jenis Data (Transact-SQL).
Pernyataan WRITETEXT dan UPDATETEXT saat menyisipkan atau menambahkan data baru ke textdalam kolom jenis data , ntext, dan image . Perhatikan bahwa pengelogan minimal tidak digunakan saat nilai yang ada diperbarui.

Catatan

Pernyataan WRITETEXT dan UPDATETEXT tidak digunakan lagi, jadi Anda harus menghindari penggunaannya 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 INDEKS Operasi REBUILD atau DBCC DBREINDEX.
  
  Catatan
  
  Pernyataan DBCC DBREINDEX tidak digunakan lagi sehingga Anda harus menghindari penggunaannya di aplikasi baru.
- DROP INDEX membangun kembali tumpukan baru (jika berlaku).
  
  Catatan
  
  Dealokasi halaman indeks selama operasi DROP INDEX selalu dicatat sepenuhnya.