Bagikan melalui

Memecahkan masalah kesalahan log transaksi dengan Azure SQL Database

  • Artikel

Berlaku untuk: Azure SQL Database

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 Database, 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 berfokus pada Azure SQL Database. Azure SQL Database didasarkan pada versi stabil terbaru dari mesin database Microsoft SQL Server, sehingga banyak konten serupa, meskipun opsi dan alat pemecahan masalah mungkin berbeda dari SQL Server.

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

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 Database, pencadangan log transaksi diambil secara otomatis. Untuk frekuensi, retensi, dan informasi selengkapnya, lihat Pencadangan otomatis.

Ruang disk kosong, pertumbuhan file database, dan lokasi file juga dikelola, sehingga penyebab umum dan resolusi masalah log transaksi berbeda dari SQL Server.

Mirip dengan SQL Server, log transaksi untuk setiap database dipotong setiap kali pencadangan log berhasil diselesaikan. Pemotongan meninggalkan ruang kosong dalam file log, yang kemudian dapat digunakan untuk transaksi baru. Ketika {i>file file

Untuk informasi tentang ukuran log transaksi, lihat:

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;

Untuk Azure SQL Database, disarankan untuk menyambungkan ke database pengguna tertentu, bukan master database, untuk menjalankan kueri ini.

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. Tidak.
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 Database, ini dapat terjadi jika penangkapan data perubahan (CDC) diaktifkan. Kueri sys.dm_cdc_errors dan atasi kesalahan. 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 NUL, permintaan tidak aktif tetapi memiliki transaksi tertunggak. 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
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, 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 kumpulan elastis Azure SQL Database, mengosongkan ruang untuk kumpulan elastis mungkin menjadi bagian dari solusi. Namun, menyelesaikan akar kondisi pemblokiran pemotongan {i>file

Untuk informasi selengkapnya tentang mengelola ruang file database dan kumpulan elastis, lihat Mengelola ruang file untuk database di Azure SQL Database.

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.

Untuk mengatasi masalah ini, coba metode berikut ini:

  1. 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 SQL Database.
  2. 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.
  3. 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.
  4. 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).

Catatan

Untuk informasi selengkapnya tentang kesalahan tata kelola sumber daya lainnya, lihat Kesalahan tata kelola sumber daya.

Langkah berikutnya