Risolvere gli errori del log delle transazioni con database SQL di Azure

Articolo
03/18/2024

È 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, gestito dal database SQL di Azure, supera la soglia di spazio e non può continuare ad accettare nuove transazioni. Questi errori sono simili ai problemi relativi a un log delle transazioni completo in SQL Server, ma hanno risoluzioni diverse in SQL Server, database SQL di Azure e Istanza gestita di SQL di Azure.

Nota

Questo articolo è incentrato sul database SQL di Azure. Database SQL di Azure e Istanza gestita di SQL di Azure si basano sulla versione stabile più recente del motore di database di Microsoft SQL Server, quindi gran parte del contenuto è simile anche se le opzioni e gli strumenti per la risoluzione dei problemi possono differire da SQL Server.

Per altre informazioni sulla risoluzione dei problemi relativi a un log delle transazioni in Istanza gestita di SQL di Azure, vedere Risolvere gli errori del log delle transazioni con Istanza gestita di SQL di Azure.

Per altre informazioni sulla risoluzione dei problemi relativi a un log delle transazioni in SQL Server, vedere Risolvere i problemi relativi a un log delle transazioni completo (errore di SQL Server 9002).

Backup automatizzati e log delle transazioni

In database SQL di Azure, i backup del log delle transazioni vengono eseguiti automaticamente. Per frequenza, conservazione e ulteriori informazioni, vedere Backup automatici.

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 di database SQL di Azure, le nuove transazioni di scrittura hanno esito negativo.

Per altre informazioni sul log delle transazioni, vedere:

Per i limiti delle risorse vCore per un database singolo, vedere Limiti delle risorse per database singoli secondo il modello di acquisto basato su vCore.
Per i limiti delle risorse vCore per i pool elastici, vedere Limiti delle risorse per pool elastici usando il modello di acquisto basato su vCore.
Per i limiti delle risorse DTU per un database singolo, vedere Limiti delle risorse per database singoli secondo il modello di acquisto basato su DTU.
Per i limiti delle risorse DTU per i pool elastici, vedere Limiti delle risorse per i pool elastici secondo il modello di acquisto basato su DTU.

Troncamento impedito del log delle transazioni

Per capire cosa impedisce il troncamento del log, fare riferimento a log_reuse_wait_desc in sys.databases. L'attesa di riutilizzo del log fornisce le condizioni o impedisce il troncamento del log delle transazioni da parte di un normale backup del log. Per altre informazioni, vedere sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Per database SQL di Azure, è 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:

log_reuse_wait_desc	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 necessaria a meno che non sia sostenuta. Se sostenuta, inviare una richiesta di supporto con il supporto di Azure.
BACKUP DI LOG	È necessario un backup del log.	Nessuna risposta necessaria a meno che non sia sostenuta. Se sostenuta, inviare una richiesta di supporto con il supporto di Azure.
BACKUP O RIPRISTINO ATTIVI	È in corso un backup del database.	Nessuna risposta necessaria a meno che non sia sostenuta. Se sostenuta, inviare una richiesta di supporto con il supporto di Azure.
TRANSAZIONE ATTIVA	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. Vedere la sezione successiva.
REPLICA	In database SQL di Azure questo problema può verificarsi se change data capture (CDC) è abilitato.	Eseguire la query sys.dm_cdc_errors e risolvere gli errori. Se non è risolvibile, inviare una richiesta di supporto con il supporto di Azure.
AVAILABILITY_REPLICA	La sincronizzazione con la replica secondaria è in corso.	Nessuna risposta necessaria a meno che non sia sostenuta. Se sostenuta, inviare una richiesta di supporto con il supporto di Azure.

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.

Eseguire questa query di esempio per trovare transazioni non inviate 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, cercare request_blocked_by. Per altre informazioni, vedere Raccogliere informazioni di blocco.
Restituisce il testo del testo o del buffer di input della richiesta corrente, utilizzando le DMV sys.dm_exec_sql_text o sys.dm_exec_input_buffer. Se i dati restituiti dal campo text di sys.dm_exec_sql_text è NULL, la richiesta non è attiva ma ha una transazione in sospeso. In tal caso, il campo event_info 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 non viene troncato in database SQL di Azure pool elastici, lo spazio disponibile per il pool elastico può far 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 dello spazio può fornire solo una soluzione temporanea fino a quando il log delle transazioni non cresce di nuovo.

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

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:

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 eseguite immediatamente implementando l'invio in batch o la suddivisione in più transazioni più piccole. Per altre informazioni, vedere Come usare l'invio in batch per migliorare le prestazioni delle applicazioni di database SQL.
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 Creazione di tabelle e indici partizionati.
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.
In caso di ricompilazione di un indice con l'istruzione ALTER INDEX, provare a 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.