Risolvere gli errori del log delle transazioni con Istanza gestita di SQL di Azure

Articolo
03/20/2024

Si applica a: Istanza gestita di SQL di Azure SQL

È 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 Istanza gestita di SQL di Azure, supera le soglie di 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, database SQL di Azure e Istanza gestita di SQL di Azure.

Nota

Questo articolo è incentrato su Istanza gestita di SQL di Azure. 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 database SQL di Azure, vedere Risolvere gli errori del log delle transazioni con database 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

I backup del log delle transazioni in Istanza gestita di SQL di Azure vengono eseguiti automaticamente. Per informazioni su frequenza, conservazione e di altro tipo, vedere i Backup automatizzati. Per tenere traccia del momento in cui sono stati eseguiti backup automatizzati in un'istanza gestita di SQL, vedere Monitorare l'attività di backup.

Il percorso e il nome dei file di database non possono essere gestiti, ma gli amministratori possono gestire i file di database e le impostazioni di aumento automatico dei file. Le cause e le soluzioni 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 un backup del log viene completato correttamente. Il troncamento del log elimina i file di log virtuali (VLF) inattivi dal log delle transazioni, liberando spazio all'interno del file, senza modificare le dimensioni del file su disco. Lo spazio vuoto nel file di log può quindi essere usato per le 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 Istanza gestita di SQL di Azure, le nuove transazioni di scrittura hanno esito negativo.

In Istanza gestita di SQL di Azure è possibile acquistare spazio di archiviazione del componente aggiuntivo, indipendentemente dal calcolo, fino a un limite. Per altre informazioni, vedere Gestione file per liberare più spazio.

Prevenzione del troncamento del log delle transazioni

Per scoprire cosa impedisce il troncamento del log in un dato caso, 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;

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. Rare.	Nessuna risposta richiesta salvo sospensione. In caso di sospensione, inviare una richiesta di supporto al supporto tecnico di Azure.
LOG BACKUP	È necessario un backup del log.	Nessuna risposta richiesta salvo sospensione. In caso di sospensione, inviare una richiesta di supporto al supporto tecnico di Azure.
ACTIVE BACKUP OR RESTORE	È in corso un backup del database.	Nessuna risposta richiesta salvo sospensione. In caso di sospensione, inviare una richiesta di supporto al supporto tecnico di Azure.
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 Istanza gestita di SQL di Azure ciò può verificarsi se la replica o CDC è abilitata.	In caso di sospensione, analizzare gli agenti coinvolti in CDC o nella 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 consultare la Risoluzione dei problemi di replica transazionale. In caso di mancata risoluzione, inviare una richiesta di supporto al supporto tecnico di Azure.
AVAILABILITY_REPLICA	La sincronizzazione con la replica secondaria è in corso.	Nessuna risposta richiesta salvo sospensione. In caso di sospensione, inviare una richiesta di supporto al 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 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 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 sono NULL, la richiesta non è attiva ma presenta 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
, 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 Istanza gestita di SQL di Azure, lo spazio disponibile potrebbe 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, questo può fornire solo una soluzione temporanea fino a quando il log delle transazioni non cresce di nuovo.

In Istanza gestita di SQL di Azure è possibile acquistare spazio di archiviazione del componente aggiuntivo, indipendentemente dal calcolo, fino a un limite. Ad esempio, nel portale di Azure, accedere alla pagina Calcolo e archiviazione per aumentare Archiviazione in GB. Per informazioni sui limiti delle dimensioni del log delle transazioni, vedere Limiti delle risorse per Istanza gestita di SQL. Per altre informazioni, vedere Gestire lo spazio file per i database in Istanza gestita di SQL di Azure.

Il costo dell'archivio di backup non viene dedotto dallo spazio di archiviazione dell'istanza gestita di SQL. L'archivio di backup è indipendente dallo spazio di archiviazione dell'istanza e non ha dimensioni limitate.

Errore 9002: il log delle transazioni per il database è pieno

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

L'Errore 9002 si verifica in SQL Server e in Istanza gestita di SQL di Azure per gli stessi motivi.

La risposta appropriata a un log delle transazioni pieno dipende in parte dalle condizioni che ne hanno causato il riempimento.

Per risolvere l'errore 9002, procedere con i metodi seguenti:

Il log delle transazioni non viene troncato e aumenta fino a riempire tutto lo spazio disponibile.
- Poiché i backup del log delle transazioni in Istanza gestita di SQL di Azure sono automatici, è necessario impedire il troncamento dell'attività del log delle transazioni. La replica incompleta, CDC o la sincronizzazione dei gruppi di disponibilità potrebbero impedire il troncamento; vedere Prevenzione del troncamento del log delle transazioni.
Le dimensioni di archiviazione riservate dell'istanza gestita di SQL sono complete e il log delle transazioni non può aumentare.
- Aggiungere spazio fino al limite di risorse. Vedere Gestione file per liberare più spazio.
Le dimensioni del log sono impostate su un valore massimo fisso o l'aumento automatico è disabilitato.
- Vedere le proprietà MAXSIZE e FILEGROWTH in ALTER DATABASE e gruppi file.

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.

Sebbene l'errore 9002 sia più comune dell'errore 40552 in Istanza gestita di SQL di Azure, entrambi possono verificarsi.

Per risolvere l'errore 40552, 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.
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) moltiplicato 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 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).

Condividi tramite