Condividi tramite

Risolvere gli errori del log delle transazioni con Azure SQL Database

Applica a:Azure SQL Database

È possibile che vengano visualizzati errori 9002 o 40552 quando il log delle transazioni è pieno e non può accettare nuove transazioni. Questi errori si verificano quando il log delle transazioni del database, gestito da Azure SQL Database, supera le soglie per lo spazio e non può continuare ad accettare transazioni. Questi errori sono simili ai problemi relativi a un log delle transazioni completo in SQL Server, ma hanno risoluzioni diverse in SQL Server, Azure SQL Database e Azure SQL Managed Instance.

Nota

Questo articolo è incentrato sulle Azure SQL Database. Azure SQL Database si basa sulla versione stabile più recente del motore di database di Microsoft SQL Server, la maggior parte del contenuto è simile, anche se le opzioni e gli strumenti per la risoluzione dei problemi potrebbero differire da SQL Server.

Per ulteriori informazioni sulla risoluzione dei problemi relativi a un log delle transazioni in Azure SQL Managed Instance, consultare Risolvere i problemi del log delle transazioni con Azure SQL Managed Instance.

Per altre informazioni sulla risoluzione dei problemi di un log delle transazioni in SQL Server, vedere Risoluzione dei problemi di un log delle transazioni completo (SQL Server Errore 9002).

Backup automatizzati e log delle transazioni

In Azure SQL Database, i backup del log delle transazioni vengono eseguiti automaticamente. Per frequenza, conservazione e altre informazioni, vedere Backup automatizzati.

Vengono gestiti anche lo spazio libero su disco, la crescita dei file di database e il percorso dei file, pertanto le cause e le soluzioni tipiche dei problemi del log delle transazioni sono diverse da SQL Server.

Analogamente a SQL Server, il log delle transazioni per ogni database viene troncato ogni volta che un backup del log viene completato correttamente. Il troncamento lascia spazio vuoto nel file di log, che può essere usato per nuove transazioni. Quando il file di log non può essere troncato dai backup del log, aumenta in modo da accettare nuove transazioni. Se il file di log raggiunge il limite massimo in Azure SQL Database, le nuove transazioni di scrittura hanno esito negativo.

Per informazioni sulle dimensioni del log delle transazioni, vedere:

Troncamento del log delle transazioni impedito

Per individuare cosa impedisce il troncamento del log in un determinato caso, fare riferimento a log_reuse_wait_desc in sys.databases. L'attesa di riutilizzo del log informa su quali condizioni o cause impediscono che il log delle transazioni venga troncato da un normale backup del log. Per altre informazioni, vedere sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Per Azure SQL Database, è consigliabile connettersi a un database utente specifico, anziché al database master, per eseguire questa query.

I valori seguenti di log_reuse_wait_desc in sys.databases possono indicare il motivo per cui viene impedito il troncamento del log delle transazioni del database:

descrizione_attesa_riutilizzo_log Diagnosi Risposta richiesta
NOTHING Stato tipico. Non c'è nulla che impedisca il troncamento del log. No
CHECKPOINT Per il troncamento del log è necessario un checkpoint. Raro. Nessuna risposta richiesta a meno che non sia continuativa. Se è sostenuta, inviare una richiesta di supporto a Azure Support.
LOG BACKUP È necessario un backup del log. Nessuna risposta richiesta a meno che non sia continuativa. Se il problema persiste, inoltrare una richiesta di supporto con Azure Support.
ACTIVE BACKUP OR RESTORE È in corso un backup del database. Nessuna risposta richiesta a meno che non sia continuativa. Se applicabile, inviare una richiesta di supporto a Azure Support.
ACTIVE TRANSACTION Una transazione in corso impedisce il troncamento del log. Il file di log non può essere troncato a causa di transazioni attive e/o di cui non è stato eseguito il commit. Consultare la sezione successiva.
REPLICATION In Azure SQL Database questo problema può verificarsi se change data capture (CDC) è abilitato. Eseguire query sys.dm_cdc_errors e risolvere gli errori. Se non è risolvibile, inviare una richiesta di supporto con Azure supporto.
AVAILABILITY_REPLICA La sincronizzazione con la replica secondaria è in corso. Nessuna risposta richiesta a meno che non sia continuativa. Se il problema persiste, inviare una richiesta di supporto a Azure Support.

Troncamento del log impedito da una transazione attiva

Lo scenario più comune per un log delle transazioni che non può accettare nuove transazioni è una transazione a esecuzione prolungata o bloccata.

Esegui questa query di esempio per trovare transazioni non confermate o attive e le relative proprietà.

  • Restituisce informazioni sulle proprietà delle transazioni, da sys.dm_tran_active_transactions.
  • Restituisce le informazioni di connessione della sessione, da sys.dm_exec_sessions.
  • Restituisce informazioni sulla richiesta (per le richieste attive), da sys.dm_exec_requests. Questa query può essere usata anche per identificare le sessioni bloccate; cerca il request_blocked_by. Per altre informazioni, vedere Raccogliere informazioni di blocco.
  • Restituisce il testo o il testo del buffer di input della richiesta corrente, utilizzando i sys.dm_exec_sql_text o sys.dm_exec_input_buffer DMV. Se i dati restituiti dal text campo di sys.dm_exec_sql_text è NULL, la richiesta non è attiva ma ha una transazione in sospeso. In tal caso, il event_info campo di sys.dm_exec_input_buffer contiene l'ultima istruzione passata al motore di 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;

Gestione file per liberare più spazio

Se il log delle transazioni viene impedito di essere troncato nei pool elastici di Azure SQL Database, liberare spazio per il pool elastico può essere parte della soluzione. Tuttavia, la risoluzione della radice della condizione che blocca il troncamento del file di log delle transazioni è la chiave. In alcuni casi, la creazione temporanea di più spazio su disco consente il completamento delle transazioni a esecuzione prolungata, rimuovendo la condizione che blocca il troncamento del file di log delle transazioni con un normale backup del log delle transazioni. Tuttavia, liberare spazio potrebbe fornire solo un sollievo temporaneo fino a quando il log delle transazioni non cresce nuovamente.

Per altre informazioni sulla gestione dello spazio file dei database e dei pool elastici, vedere Gestire lo spazio dei file per i database in Azure SQL Database.

Errore 40552: Sessione terminata a causa di un utilizzo eccessivo dello spazio del log delle transazioni

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

Per risolvere il problema, procedere con i metodi seguenti:

  1. Il problema può verificarsi in qualsiasi operazione DML, ad esempio inserimento, aggiornamento o eliminazione. Esaminare la transazione per evitare scritture non necessarie. Provare a ridurre il numero di righe elaborate direttamente implementando il batching o suddividendo in più transazioni più piccole. Per altre informazioni, vedere Come usare l'invio in batch per migliorare le prestazioni dell'applicazione del database SQL.
  2. Il problema può verificarsi a causa di operazioni di ricompilazione dell'indice. Per evitare questo problema, assicurarsi che la formula seguente sia vera: (numero di righe interessate nella tabella) moltiplicate per (la dimensione media del campo aggiornata in byte + 80) < 2 gigabyte (GB). Per le tabelle di grandi dimensioni, è consigliabile creare partizioni ed eseguire la manutenzione degli indici solo in alcune partizioni della tabella. Per altre informazioni, vedere Creare tabelle e indici partizionati.
  3. Se si eseguono inserimenti bulk usando l'utilità bcp.exe o la classe System.Data.SqlClient.SqlBulkCopy, provare a usare le opzioni -b batchsize o BatchSize per limitare il numero di righe copiate nel server in ogni transazione. Per altre informazioni, vedere bcp Utility.
  4. Se si sta ricompilando un indice con l'istruzione ALTER INDEX, usare le opzioni SORT_IN_TEMPDB = ON, ONLINE = ON e RESUMABLE=ON. Con gli indici ripristinabili, il troncamento del log è più frequente. Per altre informazioni, vedere ALTER INDEX (Transact-SQL).

Nota

Per altre informazioni sugli altri errori di governance delle risorse, vedere Errori di governance delle risorse.

Contenuti correlati

  • Last updated on