Risoluzione degli errori del log delle transazioni con Azure SQL Database e Istanza gestita di SQL di Azure

Si applica a: Database SQL di Azure Istanza gestita di SQL di Azure

È 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 di database, gestito da Azure SQL Database o Istanza gestita di SQL di Azure, 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 nel database Azure SQL o Istanza gestita di SQL di Azure.

Nota

Questo articolo è incentrato su database Azure SQL e Istanza gestita di SQL di Azure. Azure SQL Database e Istanza gestita di SQL di Azure si basano sulla versione stabile più recente del motore di database Microsoft SQL Server, la maggior parte del contenuto è simile anche se le opzioni e gli strumenti per la risoluzione dei problemi possono variare. 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 (SQL Server errore 9002).

Backup automatizzati e log delle transazioni

Esistono alcune differenze principali in Azure SQL Database e Istanza gestita di SQL di Azure in merito alla gestione dello spazio file del database.

  • In Azure SQL Database o Istanza gestita di SQL di Azure, il backup del log delle transazioni viene eseguito automaticamente. Per frequenza, conservazione e altre informazioni, vedere Backup automatizzati - Azure SQL Database & Istanza gestita di SQL.
  • In Azure SQL Database, spazio su disco libero, crescita dei file di database e percorso file vengono gestiti anche, in modo che le cause e le risoluzioni tipiche dei problemi del log delle transazioni siano diverse da SQL Server.
  • In Istanza gestita di SQL di Azure non è possibile gestire il percorso e il nome dei file di database, ma gli amministratori possono gestire i file di database e le impostazioni di aumento automatico dei file. Le cause e le risoluzioni tipiche dei problemi del log delle transazioni sono simili a SQL Server.

Analogamente a SQL Server, il log delle transazioni per ogni database viene troncato ogni volta che viene eseguito un backup del log. Il troncamento lascia spazio vuoto nel file di log, che può quindi accedere a nuove transazioni. Quando il file di log non può essere troncato dai backup del log, il file di log cresce per gestire le nuove transazioni. Se il file di log raggiunge i limiti massimi in Azure SQL Database o Istanza gestita di SQL di Azure, non è possibile accettare nuove transazioni. Questo è uno scenario molto insolito.

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 le condizioni o le cause che 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;

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

log_reuse_wait_desc Diagnosi Risposta necessaria
NIENTE Stato tipico. Non c'è nulla che blocca il log da troncare. No.
CHECKPOINT È necessario un checkpoint per il troncamento del log. Raro. Nessuna risposta necessaria a meno che non sia stata sostenuta. In caso di supporto, inviare una richiesta di supporto con il supporto tecnico di Azure.
LOG BACKUP Un backup del log è in corso. Nessuna risposta necessaria a meno che non sia stata sostenuta. In caso di supporto, inviare una richiesta di supporto con il supporto tecnico di Azure.
BACKUP O RIPRISTINO ATTIVI Un backup del database è in corso. Nessuna risposta necessaria a meno che non sia stata sostenuta. In caso di supporto, inviare una richiesta di supporto con il supporto tecnico di Azure.
TRANSAZIONE ATTIVA Una transazione in corso impedisce il troncamento del log. Impossibile troncare il file di log a causa di transazioni attive e/o non inviate. Vedere la sezione successiva.
REPLICA In Azure SQL Database, probabilmente a causa della funzionalità di change data capture (CDC).
In Istanza gestita di SQL di Azure, a causa della replica o del CDC.
In Azure SQL Database eseguire query sys.dm_cdc_errors e risolvere gli errori. Se non è possibile, inviare una richiesta di supporto con il supporto tecnico di Azure.
In Istanza gestita di SQL di Azure, se sostenuta, analizzare gli agenti coinvolti in CDC o replica. Per la risoluzione dei problemi di CDC, eseguire query sui processi in msdb.dbo.cdc_jobs. Se non presente, aggiungere tramite sys.sp_cdc_add_job. Per la replica, prendere in considerazione la risoluzione dei problemi di replica transazionale. Se non è possibile, inviare una richiesta di supporto con il supporto tecnico di Azure.
AVAILABILITY_REPLICA La sincronizzazione alla replica secondaria è in corso. Nessuna risposta necessaria a meno che non sia stata sostenuta. In caso di supporto, inviare una richiesta di supporto con il supporto tecnico 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 le 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 sul blocco, vedere Raccogliere informazioni di blocco.
  • Restituisce il testo o il buffer di input della richiesta corrente usando 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 conterrà l'ultima stringa di comando passata al motore di sys.dm_exec_input_buffer 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 --uncommitted and unrolled back transactions open. 
, 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, liberare più spazio nell'allocazione dei file di database 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 consentirà il completamento di una transazione a esecuzione prolungata, rimuovendo la condizione che blocca il file di log delle transazioni dal troncamento con un normale backup del log delle transazioni. Tuttavia, liberando spazio nell'allocazione può fornire solo un rilievo temporaneo fino a quando il log delle transazioni aumenta 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 Azure SQL Database.

Errore 40552: la sessione è stata 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 a causa di operazioni di inserimento, aggiornamento o eliminazione. Esaminare la transazione per evitare scritture non necessarie. Provare a ridurre il numero di righe eseguite immediatamente tramite l'implementazione di 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.
  2. Il problema può verificarsi a causa di operazioni di ricompilazione dell'indice. Per evitare questo problema, assicurarsi che la formula seguente sia true: (numero di righe interessate nella tabella) moltiplicate per (la dimensione media del campo aggiornata in byte + 80) < 2 gigabyte (GB). Per tabelle di grandi dimensioni, è consigliabile creare partizioni ed eseguire la manutenzione dell'indice solo in alcune partizioni della tabella. Per altre informazioni, vedere Creazione di 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 ricompila un indice con l'istruzione ALTER INDEX , usare le SORT_IN_TEMPDB = ON opzioni e ONLINE = ON . Per altre informazioni, vedere ALTER INDEX (Transact-SQL).

Nota

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

Passaggi successivi

Per informazioni sulle dimensioni del log delle transazioni, vedere: