Memecahkan masalah kesalahan log transaksi dengan Azure SQL Database

Aplikasi ke:Azure SQL Database

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 Database, melebihi ambang batas untuk ruang dan tidak dapat terus menerima transaksi. Kesalahan ini mirip dengan masalah dengan log transaksi penuh dalam SQL Server, tetapi memiliki resolusi yang berbeda dalam SQL Server, Azure SQL Database, dan Azure SQL Managed Instance.

Catatan

Artikel ini difokuskan pada Azure SQL Database. Azure SQL Database didasarkan pada versi stabil terbaru 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 kesalahan log transaksi di Azure SQL Managed Instance, lihat Troubleshoot transaction log errors with Azure SQL Managed Instance.

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

Pencadangan otomatis dan log transaksi

Dalam 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 file log tidak dapat dipotong oleh cadangan log, file log tumbuh untuk mengakomodasi transaksi baru. Jika file log tumbuh hingga batas maksimumnya dalam Azure SQL Database, transaksi tulis baru gagal.

Untuk informasi tentang ukuran log transaksi, lihat:

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 (Transact-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:

deskripsi_tunggu_penggunaan_ulang_log Diagnosa Diperlukan respons
NOTHING Status umum. Tidak ada yang memblokir log dari pemotongan. Tidak.
CHECKPOINT Titik pemeriksaan diperlukan untuk pemotongan log. Jarang. Tidak ada respons yang diperlukan kecuali diperlukan berkelanjutan. Jika berkelanjutan, ajukan permintaan dukungan dengan Azure Dukungan.
LOG BACKUP Pencadangan log diperlukan. Tidak ada respons yang diperlukan kecuali diperlukan berkelanjutan. Jika berkelanjutan, ajukan permintaan dukungan dengan Azure Dukungan.
ACTIVE BACKUP OR RESTORE Pencadangan database sedang berlangsung. Tidak ada respons yang diperlukan kecuali diperlukan berkelanjutan. Jika berkelanjutan, ajukan permintaan dukungan dengan Azure Dukungan.
ACTIVE TRANSACTION Transaksi yang sedang berlangsung mencegah pemotongan log. File log tidak dapat dipangkas karena transaksi aktif dan/atau belum diselesaikan. Lihat bagian berikutnya.
REPLICATION Dalam Azure SQL Database, ini mungkin terjadi jika pertukaran pengambilan data (CDC) diaktifkan. Kueri sys.dm_cdc_errors dan atasi kesalahan. Jika tidak dapat diselesaikan, ajukan permintaan dukungan dengan Azure Dukungan.
AVAILABILITY_REPLICA Sinkronisasi ke replika sekunder sedang berlangsung. Tidak ada respons yang diperlukan kecuali diperlukan berkelanjutan. Jika berkelanjutan, ajukan permintaan dukungan dengan Azure Dukungan.

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 Mengumpulkan informasi pemblokiran.
  • Mengembalikan teks perintah saat ini atau teks buffer input, menggunakan DMV sys.dm_exec_sql_text atau sys.dm_exec_input_buffer. Jika data yang dikembalikan dari text untuk bidang 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
, 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 file untuk mengosongkan lebih banyak ruang

Jika log transaksi dicegah agar tidak terpotong di dalam kumpulan elastis Azure SQL Database, mengosongkan ruang di dalam kumpulan elastis dapat menjadi bagian dari solusinya. Namun, menyelesaikan penyebab utama yang menghalangi pemotongan file log transaksi adalah kuncinya. Dalam beberapa kasus, menciptakan ruang disk sementara memungkinkan transaksi jangka panjang dapat diselesaikan, menghilangkan kondisi yang menghalangi file log transaksi dari pemotongan melalui pencadangan log transaksi normal. Namun, mengosongkan ruang mungkin hanya memberikan bantuan sementara sampai log transaksi tumbuh lagi.

Untuk informasi selengkapnya tentang mengelola ruang file database dan kumpulan elastis, lihat Kelola 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 diproses secara langsung dengan menerapkan batching atau dengan membagi 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 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.
  4. Jika Anda membangun ulang indeks dengan pernyataan ALTER INDEX, gunakan opsi SORT_IN_TEMPDB = ON, ONLINE = ON, dan RESUMABLE=ON. Dengan indeks yang dapat dilanjutkan, pemotongan log lebih sering. Untuk informasi selengkapnya, lihat ALTER INDEX (Transact-SQL).

Catatan

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

Konten terkait

  • Last updated on