Resolver erros no registo de transações com Azure SQL Database

Aplica-se a: Azure SQL Database

Podes ver os erros 9002 ou 40552 quando o registo de transações está cheio e não consegues aceitar novas transações. Estes erros ocorrem quando o registo de transações da base de dados, gerido pelo Azure SQL Database, ultrapassa os limiares de espaço e não consegue continuar a aceitar transações. Estes erros são semelhantes a problemas com um registo completo de transações no SQL Server, mas têm resoluções diferentes no SQL Server, Azure SQL Database e Azure SQL Managed Instance.

Backups automatizados e o log de transações

No Azure SQL Database, as cópias de segurança dos registos de transações são feitas automaticamente. Para frequência, retenção e mais informações, consulte Backups automatizados.

O espaço livre em disco, o crescimento de ficheiros na base de dados e a localização dos ficheiros também são geridos, pelo que as causas e resoluções típicas dos problemas nos registos de transações são diferentes do SQL Server.

Semelhante ao SQL Server, o log de transações de cada base de dados é truncado sempre que um backup do log é concluído com sucesso. O truncamento deixa espaço vazio no arquivo de log, que pode ser usado para novas transações. Quando o ficheiro de registo não pode ser truncado por backups de registo, o ficheiro de registo cresce para acomodar novas transações. Se o ficheiro de registo atingir o seu limite máximo no Azure SQL Database, as novas transações de escrita falham.

Para obter informações sobre tamanhos de log de transações, consulte:

• Para limites de recursos vCore para uma única base de dados, veja limites de recursos para bases de dados individuais usando o modelo de compra vCore.
• Para limites de recursos vCore para pools elásticos, veja limites de recursos para pools elásticos usando o modelo de compra vCore.
• Para limites de recursos da DTU para uma única base de dados, veja limites de recursos para bases de dados individuais usando o modelo de compra da DTU.
• Para limites de recursos DTU para pools elásticos, veja limites de recursos para pools elásticos usando o modelo de compra DTU.

Impedido o truncamento do log de transações

Para descobrir o que impede o truncamento logarítmico num dado caso, consulte log_reuse_wait_desc em sys.databases. A espera de reutilização de log informa quais condições ou causas estão impedindo que o log de transações seja truncado por um backup de log regular. Para mais informações, consulte sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Para a Azure SQL Database, recomenda-se ligar-se a uma base de dados de utilizador específica, em vez da master base de dados, para executar esta consulta.

Os seguintes valores de log_reuse_wait_desc em sys.databases podem indicar a razão pela qual o truncamento do log de transações na base de dados está a ser prevenido:

Truncamento de log impedido por uma transação ativa

O cenário mais comum para um registo de transações que não aceita novas transações é uma transação de longa duração ou bloqueada.

Execute esta consulta de exemplo para localizar transações não confirmadas ou ativas e suas propriedades.

• Retorna informações sobre propriedades de transação de sys.dm_tran_active_transactions.
• Devolve informações de ligação à sessão, de sys.dm_exec_sessions.
• Devolve informações sobre o pedido (para pedidos ativos) a partir de sys.dm_exec_requests. Esta consulta também pode ser usada para identificar sessões que estão a ser bloqueadas, procure o request_blocked_by. Para mais informações, consulte Coletar informações de bloqueio.
• Retorna o texto da requisição atual ou o texto do buffer de entrada, usando as DMVs sys.dm_exec_sql_text ou sys.dm_exec_input_buffer. Se os dados devolvidos pelo text campo de sys.dm_exec_sql_text forem NULL, o pedido não está ativo mas tem uma transação pendente. Nesse caso, o event_info campo de sys.dm_exec_input_buffer contém a última instrução passada para o motor da base de dados.

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;

Gestão de ficheiros para libertar mais espaço

Se o registo de transações for impedido de truncar nos pools elásticos do Azure SQL Database, libertar espaço para o pool elástico pode fazer parte da solução. No entanto, resolver a raiz da condição que bloqueia o truncamento do arquivo de log de transações é fundamental. Em alguns casos, criar temporariamente mais espaço em disco permite que transações de longa duração sejam concluídas, removendo a condição que impede que o arquivo de log de transações seja truncado durante um backup normal de log de transações. No entanto, liberar espaço pode fornecer apenas alívio temporário até que o log de transações cresça novamente.

Para mais informações sobre a gestão do espaço de ficheiros de bases de dados e pools elásticos, consulte Gerencie o espaço de ficheiros para bases de dados em Azure SQL Database.

Erro 40552: A sessão foi encerrada devido ao uso excessivo de espaço no log de transações

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

Para resolver esse problema, tente os seguintes métodos:

1. O problema pode ocorrer em qualquer operação DML, como inserir, atualizar ou excluir. Revise a transação para evitar gravações desnecessárias. Tente reduzir o número de linhas que sejam processadas de imediato através da implementação de lotes ou ao dividir em várias transações menores. Para mais informações, consulte Como usar o batching para melhorar o desempenho de aplicações SQL Database.
2. O problema pode ocorrer devido a operações de reconstrução de índice. Para evitar este problema, certifique-se de que a seguinte fórmula é verdadeira: (número de linhas afetadas na tabela) multiplicado por (o tamanho médio do campo atualizado em bytes + 80) < 2 gigabytes (GB). Para tabelas grandes, considere criar partições e executar a manutenção do índice apenas em algumas partições da tabela. Para mais informações, consulte Criar Tabelas e Índices Particionados.
3. Se realizar inserções em massa usando a bcp.exe ferramenta ou a SqlBulkCopy classe (disponível em ambas Microsoft.Data.SqlClient e System.Data.SqlClient), tente usar as opções -b batchsize ou BatchSize para limitar o número de linhas copiadas para o servidor em cada transação. Para obter mais informações, consulte bcp Utility.
4. Se estiver a reconstruir um índice com ALTER INDEX instrução, use as opções SORT_IN_TEMPDB = ON, ONLINE = ON, e RESUMABLE=ON. Com índices retomáveis, o truncamento de log é mais frequente. Para mais informações, consulte ALTER INDEX (Transact-SQL).

Conteúdo relacionado

• Compreender e resolver problemas de bloqueio do Azure SQL Database
• Resolução de problemas de conectividade e outros erros com o Azure SQL Database e o Azure SQL Managed Instance
• Resolver erros de ligação transitória em Azure SQL Database e SQL Managed Instance
• Resolver problemas de atraso de reexecução da geo-replicação
• Resolução de erros de memória
• Vídeo: Melhores Práticas para Carregamento de Dados no Azure SQL Database