Memecahkan masalah kesalahan log transaksi dengan Azure SQL Managed Instance

Berlaku untuk:Azure SQL Managed Instance

Anda mungkin melihat kesalahan 9002 atau 40552 saat log transaksi penuh dan tidak dapat menerima transaksi baru. Kesalahan ini terjadi ketika log transaksi database, dikelola oleh Azure SQL Managed Instance, melebihi ambang batas untuk ruang dan tidak dapat terus menerima transaksi. Kesalahan ini mirip dengan masalah dengan log transaksi penuh di SQL Server, tetapi memiliki resolusi yang berbeda di SQL Server, Azure SQL Database, dan Azure SQL Managed Instance.

Catatan

Artikel ini difokuskan pada Azure SQL Managed Instance. Azure SQL Managed Instance didasarkan pada versi stabil terbaru mesin database Microsoft SQL Server, sehingga banyak kontennya mirip, meskipun opsi dan alat pemecahan masalah mungkin berbeda dari SQL Server.

Untuk informasi selengkapnya tentang pemecahan masalah log transaksi di Azure SQL Database, lihat Memecahkan masalah kesalahan log transaksi dengan Azure SQL Database.

Untuk selengkapnya tentang pemecahan masalah log transaksi di SQL Server, lihat Memecahkan Masalah Log Transaksi Penuh (Kesalahan SQL Server 9002).

Pencadangan otomatis dan log transaksi

Di Azure SQL Managed Instance, pencadangan log transaksi diambil secara otomatis. Untuk frekuensi, retensi, dan informasi selengkapnya, lihat Pencadangan otomatis. Untuk melacak kapan pencadangan otomatis telah dilakukan pada instans terkelola SQL, tinjau Memantau aktivitas pencadangan.

Lokasi dan nama file database tidak dapat dikelola tetapi administrator dapat mengelola file database dan pengaturan penyebaran otomatis file. Penyebab umum dan resolusi masalah log transaksi mirip dengan SQL Server.

Mirip dengan SQL Server, log transaksi untuk setiap database dipotong setiap kali pencadangan log berhasil diselesaikan. Pemotongan log menghapus file log virtual (VLF) yang tidak aktif dari log transaksi, membebaskan ruang di dalam file tetapi tidak mengubah ukuran file pada disk. Ruang kosong dalam file log kemudian dapat digunakan untuk transaksi baru. Ketika file log tidak dapat dipotong oleh cadangan log, file log tumbuh untuk mengakomodasi transaksi baru. Jika file log tumbuh hingga batas maksimumnya di Azure SQL Managed Instance, transaksi tulis baru gagal.

Pada Azure SQL Managed Instance, Anda dapat membeli penyimpanan tambahan, terpisah dari komputasi, hingga batas tertentu. Untuk informasi selengkapnya, lihat Manajemen file untuk mengosongkan lebih banyak ruang.

Pencegahan pemotongan log transaksi

Untuk mengetahui apa yang mencegah pemotongan log dalam satu kasus tertentu, lihat log_reuse_wait_desc di sys.databases. Penantian penggunaan kembali log memberi tahu Anda kondisi atau penyebab apa yang mencegah log transaksi terpotong oleh cadangan log biasa. Untuk informasi selengkapnya, lihat sys.databases (T-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Nilai log_reuse_wait_desc masuk sys.databases berikut mungkin menunjukkan alasan mengapa pemotongan log transaksi database dicegah:

log_reuse_wait_desc Diagnosa Diperlukan respons
NOTHING Keadaan tipikal Tidak ada yang memblokir log dari pemotongan. Tidak.
CHECKPOINT Titik kontrol diperlukan untuk pemotongan log. Jarang. Tidak ada respons yang diperlukan kecuali jika berlanjut. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.
PENCADANGAN LOG Pencadangan log diperlukan. Tidak ada respons yang diperlukan kecuali jika keadaan terus berlanjut. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.
PENCADANGAN ATAU PEMULIHAN AKTIF Pencadangan database sedang berlangsung. Tidak ada respons yang diperlukan kecuali jika keadaan terus berlanjut. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.
TRANSAKSI AKTIF Transaksi yang sedang berlangsung mencegah pemotongan log. File log tidak dapat dipangkas karena transaksi aktif dan/atau belum diselesaikan. Lihat bagian berikutnya.
REPLIKASI Pada Azure SQL Managed Instance, dapat terjadi jika replikasi atau CDC diaktifkan. Jika berkelanjutan, selidiki agen-agen yang terlibat dalam CDC atau replikasi. Untuk pemecahan masalah CDC, lakukan kueri pekerjaan di msdb.dbo.cdc_jobs. Jika tidak ada, tambahkan melalui sys.sp_cdc_add_job. Untuk replikasi, lihat Pemecahan masalah replikasi transaksional. Jika tidak dapat diperbaiki, ajukan permintaan dukungan dengan Dukungan Azure.
AVAILABILITY_REPLICA (Replika Ketersediaan) Sinkronisasi ke replika sekunder sedang berlangsung. Tidak ada respons yang diperlukan kecuali jika keadaan terus berlanjut. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.

Pemotongan log dicegah oleh transaksi aktif

Skenario paling umum untuk log transaksi yang tidak dapat menerima transaksi baru adalah transaksi yang berjalan lama atau diblokir.

Jalankan kueri sampel ini untuk menemukan transaksi yang tidak berkomitmen atau aktif dan propertinya.

  • Kembalikan informasi tentang properti transaksi, dari sys.dm_tran_active_transactions.
  • Mengembalikan informasi koneksi sesi, dari sys.dm_exec_sessions.
  • Kembalikan informasi permintaan (untuk permintaan aktif), dari sys.dm_exec_requests. Kueri ini juga dapat digunakan untuk mengidentifikasi sesi yang diblokir, cari request_blocked_by. Untuk informasi selengkapnya, lihat Kumpulkan Informasi Pemblokiran.
  • Mengembalikan teks dari permintaan saat ini atau teks buffer masukan, menggunakan DMV sys.dm_exec_sql_text atau sys.dm_exec_input_buffer. Jika data yang dikembalikan oleh kolom text dari sys.dm_exec_sql_text adalah NULL, permintaan tidak aktif tetapi memiliki transaksi yang tertunda. Dalam hal ini, event_info bidang sys.dm_exec_input_buffer berisi pernyataan terakhir yang diteruskan ke mesin database.
SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

Manajemen file untuk membebaskan lebih banyak ruang

Jika pemotongan log transaksi di Azure SQL Managed Instance terhalang, membebaskan ruang mungkin menjadi bagian dari solusi. Namun, menyelesaikan akar permasalahan yang menghalangi pemotongan file log transaksi adalah kuncinya. Dalam beberapa kasus, menyediakan lebih banyak ruang disk secara sementara memungkinkan transaksi yang berlangsung lama untuk menyelesaikan, sehingga menghilangkan hambatan yang mencegah file log transaksi dapat dipotong dengan pencadangan log transaksi yang normal. Namun, mengosongkan ruang dapat hanya memberikan bantuan sementara sampai log transaksi tumbuh lagi.

Pada Azure SQL Managed Instance, Anda dapat membeli penyimpanan tambahan, terpisah dari komputasi, hingga batas tertentu. Misalnya, di portal Azure, akses halaman Komputasi + penyimpanan untuk meningkatkan Penyimpanan dalam GB. Untuk informasi tentang batas ukuran log transaksi, lihat batas sumber daya untuk SQL Managed Instance. Untuk informasi selengkapnya, lihat Mengelola ruang file untuk database di Azure SQL Managed Instance.

Penyimpanan cadangan tidak dikurangi dari ruang penyimpanan instans terkelola SQL Anda. Penyimpanan cadangan independen dari ruang penyimpanan instans dan tidak dibatasi ukurannya.

Kesalahan 9002: Log transaksi untuk database penuh

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Kesalahan 9002 terjadi di SQL Server dan di Azure SQL Managed Instance karena alasan yang sama.

Respons yang sesuai terhadap log transaksi penuh tergantung pada kondisi apa yang menyebabkan log terisi.

Untuk mengatasi Kesalahan 9002, coba metode berikut:

  • Log transaksi tidak terpotong dan telah berkembang hingga memenuhi semua ruang yang tersedia.
    • Karena pencadangan log transaksi di Azure SQL Managed Instance bersifat otomatis, sesuatu yang lain harus menjaga agar aktivitas log transaksi tidak terpotong. Replikasi yang tidak lengkap, CDC, atau sinkronisasi grup ketersediaan mungkin mencegah pemotongan log transaksi, lihat Pemotongan log transaksi yang dicegah.
  • Ukuran penyimpanan cadangan instans terkelola SQL penuh, dan log transaksi tidak dapat tumbuh.
  • Ukuran Log Transaksi diatur ke nilai maksimum tetap, atau pertumbuhan otomatis dinonaktifkan, sehingga tidak dapat bertambah.

Kesalahan 40552: Sesi telah dihentikan karena penggunaan ruang log transaksi yang berlebihan

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

Meskipun Kesalahan 9002 lebih umum daripada Kesalahan 40552 di Azure SQL Managed Instance, keduanya dapat terjadi.

Untuk mengatasi Kesalahan 40552, coba metode berikut:

  • Masalah ini dapat terjadi dalam operasi DML apa pun seperti menyisipkan, memperbarui, atau menghapus. Tinjau transaksi untuk menghindari tulisan yang tidak perlu. Cobalah untuk mengurangi jumlah baris yang segera diproses dengan menerapkan pengelompokan atau membaginya menjadi beberapa transaksi yang lebih kecil. Untuk informasi selengkapnya, lihat Cara menggunakan batching untuk meningkatkan performa aplikasi.
  • Masalah ini dapat terjadi karena operasi pembangunan kembali indeks. Untuk menghindari masalah ini, pastikan rumus berikut benar: (jumlah baris yang terpengaruh dalam tabel) dikalikan (ukuran rata-rata bidang yang diperbarui dalam byte + 80) < 2 gigabyte (GB). Untuk tabel besar, pertimbangkan untuk membuat partisi dan melakukan pemeliharaan indeks hanya pada beberapa partisi tabel. Untuk mengetahui informasi selengkapnya, lihat Membuat Tabel dan Indeks yang Dipartisi.
  • Jika Anda melakukan penyisipan massal menggunakan utilitas bcp.exe atau kelas SqlBulkCopy (tersedia di keduanya Microsoft.Data.SqlClient dan System.Data.SqlClient), coba gunakan opsi -b batchsize atau BatchSize untuk membatasi jumlah baris yang disalin ke server pada setiap transaksi. Untuk informasi selengkapnya, lihat Utilitas bcp.
  • Jika Anda membangun ulang indeks dengan pernyataan ALTER INDEX, gunakan opsi SORT_IN_TEMPDB = ON, ONLINE = ON, dan RESUMABLE=ON. Dengan indeks berkelanjutan, pemotongan log lebih sering. Untuk informasi selengkapnya, lihat ALTER INDEX (T-SQL).

Langkah berikutnya

  • Last updated on