Memecahkan masalah kesalahan log transaksi dengan Azure SQL Managed Instance

  • Artikel

Berlaku untuk:Azure SQL Managed Instance

Anda mungkin melihat kesalahan 9002 atau 40552 ketika 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 serupa, 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 transaksi log

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 {i>file file

Di Azure SQL Managed Instance, Anda dapat membeli penyimpanan add-on, secara independen dari komputasi, hingga batas. Untuk informasi selengkapnya, lihat Manajemen file untuk mengosongkan lebih banyak ruang.

Pemotongan log transaksi yang dicegah

Untuk menemukan apa yang mencegah pemotongan log dalam 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 dapat menunjukkan alasan mengapa pemotongan log transaksi database dicegah:

log_reuse_wait_desc Diagnosis Diperlukan respons
NOTHING Status umum. Tidak ada yang menghalangi log dari pemotongan. Nomor.
CHECKPOINT Titik pemeriksaan diperlukan untuk pemotongan log. Jarang. Tidak ada respons yang diperlukan kecuali dipertahankan. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.
LOG BACKUP Pencadangan log diperlukan. Tidak ada respons yang diperlukan kecuali dipertahankan. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.
ACTIVE BACKUP OR RESTORE Pencadangan {i>database Tidak ada respons yang diperlukan kecuali dipertahankan. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.
ACTIVE TRANSACTION Transaksi yang sedang berlangsung mencegah pemotongan log. {i>File
REPLICATION Di Azure SQL Managed Instance, dapat terjadi jika replikasi atau CDC diaktifkan. Jika berkelanjutan, selidiki agen yang terlibat dengan 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 Sinkronisasi ke replika sekunder sedang berlangsung. Tidak ada respons yang diperlukan kecuali dipertahankan. Jika dipertahankan, ajukan permintaan dukungan dengan Dukungan Azure.

Pemotongan log dicegah oleh transaksi aktif

Skenario yang paling umum untuk log transaksi yang tidak dapat menerima transaksi baru adalah transaksi yang sudah 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 Mengumpulkan informasi pemblokiran.
  • Mengembalikan teks permintaan saat ini atau teks buffer input, menggunakan DMV sys.dm_exec_sql_text atau sys.dm_exec_input_buffer . Jika data yang dikembalikan oleh text bidang sys.dm_exec_sql_text adalah NULL, permintaan tidak aktif tetapi memiliki transaksi yang luar biasa. 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 {i>file

Jika log transaksi dicegah terpotong di Azure SQL Managed Instance, mengosongkan ruang mungkin menjadi bagian dari solusi. Namun, menyelesaikan akar kondisi pemblokiran pemotongan {i>file

Di Azure SQL Managed Instance, Anda dapat membeli penyimpanan add-on, secara independen dari komputasi, hingga batas. 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 ukurannya tidak terbatas.

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 dipotong dan telah berkembang untuk mengisi 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, 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 tumbuh.

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 dioperasikan dengan menerapkan batching atau pemisahan 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 System.Data.SqlClient.SqlBulkCopy, coba gunakan opsi -b batchsize atau BatchSize untuk membatasi jumlah baris yang disalin ke server di setiap transaksi. Untuk informasi selengkapnya, lihat Utilitas bcp.
  • Jika Anda membangun kembali indeks dengan ALTER INDEX pernyataan , gunakan SORT_IN_TEMPDB = ONopsi , ONLINE = ON, dan RESUMABLE=ON . Dengan indeks yang dapat dilanjutkan, pemotongan log lebih sering. Untuk informasi selengkapnya, lihat ALTER INDEX (T-SQL).

Langkah berikutnya