Solução de problemas de erros de log de transações com a Instância Gerenciada de SQL do Azure

  • Artigo

Aplica-se a:Instância Gerenciada de SQL do Azure

É possível que sejam exibidos os erros 9002 ou 40552 quando o log de transações estiver cheio e não puder aceitar novas transações. Esses erros ocorrem quando o log de transações do banco de dados, gerenciado pela Instância Gerenciada de SQL do Azure, excede os limites de espaço e não pode aceitar novas transações. Esses erros são semelhantes aos problemas com um log de transações completo no SQL Server, mas têm resoluções diferentes no SQL Server, no banco de dados SQL do Azure e na Instância Gerenciada de SQL do Azure.

Observação

Este artigo se concentra na Instância Gerenciada de SQL do Azure. A Instância Gerenciada de SQL do Azure é baseada na versão estável mais recente do Mecanismo de Banco de Dados do Microsoft SQL Server, portanto, grande parte do conteúdo é semelhante, embora as opções e as ferramentas de solução de problemas possam diferir do SQL Server.

Para saber mais sobre a solução de problemas do log de transações no banco de dados SQL do Azure, confira Solução de problemas de log de transações completo no Banco de Dados SQL do Azure.

Para obter mais informações sobre como solucionar problemas de um log de transações no SQL Server, consulte Solucionar problemas de log de transações cheio (Erro 9002 do SQL Server).

Backups automatizados e o log de transações

Na Instância Gerenciada de SQL do Azure, os backups de log de transações são feitos automaticamente. Para obter frequência, retenção e mais informações, confira Backups automatizados. Para acompanhar quando os backups automatizados foram feitos em uma instância gerenciada SQL, examine Monitorar a atividade de backup.

A localização e o nome dos arquivos de banco de dados não podem ser gerenciados, mas os administradores podem gerenciar arquivos de banco de dados e configurações de aumento automático de arquivos. As causas típicas e as resoluções de problemas de log de transações são semelhantes às do SQL Server.

De maneira análoga ao SQL Server, o log de transações de cada banco de dados é truncado sempre que um backup de log é feito. O truncamento de log exclui arquivos de log virtual (VLFs) inativos do log de transações, liberando espaço dentro do arquivo, mas sem alterar o tamanho do arquivo no disco. O espaço vazio no arquivo de log pode ser usado para novas transações. Quando não puder ser truncado pelos backups de log, o arquivo de log aumenta para acomodar novas transações. Se o arquivo de log aumentar até os limites máximos na Instância Gerenciada de SQL do Azure, novas transações de gravação falharão.

Na Instância Gerenciada de SQL do Azure, você pode adquirir armazenamento de complemento, independentemente da computação, até um limite. Para obter mais informações, confira Gerenciamento de arquivos para liberar mais espaço.

Truncamento do log de transações impedido

Para descobrir o que está impedindo o truncamento de log em um determinado caso, consulte log_reuse_wait_desc em sys.databases. A espera de reutilização do log informa as condições ou causas que estão impedindo que o log de transações seja truncado em um backup de log regular. Para obter mais informações, confira sys.databases (Transact-SQL).

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Os seguintes valores de log_reuse_wait_desc em sys.databases podem informar o motivo pelo qual o truncamento do log de transações do banco de dados está sendo impedido:

log_reuse_wait_desc Diagnóstico Resposta exigida
NADA Estado típico. Não há nada que bloqueie o truncamento do log. Não.
CHECKPOINT É necessário um ponto de verificação para o truncamento de log. Raro. Nenhuma resposta é necessária, a menos que essa condição se mantenha. Caso ela seja mantida, abra uma solicitação de suporte no Suporte do Azure.
BACKUP DE LOG É necessário ter um backup de log. Nenhuma resposta é necessária, a menos que essa condição se mantenha. Caso ela seja mantida, abra uma solicitação de suporte no Suporte do Azure.
BACKUP OU RESTAURAÇÃO ATIVOS Um backup de banco de dados está em andamento. Nenhuma resposta é necessária, a menos que essa condição se mantenha. Caso ela seja mantida, abra uma solicitação de suporte no Suporte do Azure.
TRANSAÇÃO ATIVA Uma transação em andamento está impedindo o truncamento de log. O arquivo de log não pode ser truncado devido a transações ativas e/ou não confirmadas. Confira a próxima seção.
REPLICAÇÃO Na Instância Gerenciada de SQL do Azure, isso poderá ocorrer se a replicação ou a CDC estiverem habilitadas. Caso a situação se mantenha, investigue os operadores envolvidos em CDA ou replicação. Para solucionar problemas de CDC, consulte trabalhos em msdb.dbo.cdc_jobs. Se não estiver presente, adicione-o usando Sys.sp_cdc_add_job. Para replicação, confira Solução de problemas de replicação transacional. Se não for possível resolver, abra uma solicitação de suporte no Suporte do Azure.
AVAILABILITY_REPLICA A sincronização para a réplica secundária está em andamento. Nenhuma resposta é necessária, a menos que essa condição se mantenha. Caso ela seja mantida, abra uma solicitação de suporte no Suporte do Azure.

Truncamento de log impedido por uma transação ativa

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

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

  • Retorna informações sobre as propriedades da transação, a partir de sys.dm_tran_active_transactions.
  • Retorna informações de conexão de sessão, a partir de sys.dm_exec_sessions.
  • Retorna informações de solicitação (para solicitações ativas), a partir de sys.dm_exec_requests. Essa consulta também pode ser usada para identificar as sessões que estão sendo bloqueadas. Pesquise request_blocked_by. Para obter mais informações, confira Coletar informações de bloqueios.
  • Retorna o texto da solicitaçã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 retornados pelo campo text de sys.dm_exec_sql_text forem NULL, a solicitação não estará ativa, mas terá uma transação pendente. Nesse caso, o campo event_info de sys.dm_exec_input_buffer conterá a última instrução transmitida ao mecanismo de banco 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
, 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;

Gerenciamento de arquivos para liberar espaço

Se o truncamento do log de transações for impedido na Instância Gerenciada de SQL do Azure, liberar espaço pode ser parte da solução. No entanto, o principal a ser feito é resolver a condição raiz que está bloqueando o truncamento do arquivo de log de transações. Em alguns casos, a criação temporária de mais espaço em disco permitirá a conclusão de transações de execução prolongada, removendo a condição que bloqueia o truncamento do arquivo de log de transações com um backup de log de transações normal. No entanto, liberar espaço pode representar apenas uma redução temporária até que o tamanho do log de transações volte a aumentar.

Na Instância Gerenciada de SQL do Azure, você pode adquirir armazenamento de complemento, independentemente da computação, até um limite. Por exemplo, no portal do Azure, acesse a página Computação + armazenamento para aumentar o Armazenamento em GB. Para obter informações sobre limites de tamanho de log de transações, confira Limites de recursos para a Instância Gerenciada de SQL. Para obter mais informações, consulte Gerenciar espaço de arquivo para bancos de dados na Instância Gerenciada de SQL do Azure.

O armazenamento de backup não é deduzido do seu espaço de armazenamento da Instância Gerenciada de SQL. O armazenamento de backup é independente do espaço de armazenamento da instância e não é limitado em tamanho.

Erro 9002: o log de transações do banco de dados está cheio

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.

O erro 9002 ocorre no SQL Server e na Instância Gerenciada de SQL do Azure pelos mesmos motivos.

A resposta apropriada quando o log de transações fica cheio depende das condições que causaram isso.

Para resolver o Erro 9002, tente os métodos a seguir.

  • O log de transações não está sendo truncado e aumentou para preencher todo o espaço disponível.
    • Como os backups de log de transações na Instância Gerenciada de SQL do Azure são automáticos, outra coisa deve estar impedindo que a atividade do log de transações seja truncada. A replicação incompleta, a CDA ou a sincronização do grupo de disponibilidade podem estar impedindo o truncamento; confira Truncamento do log de transações impedido.
  • O tamanho do armazenamento reservado da Instância Gerenciada de SQL está cheio e o log de transações não pode aumentar.
  • O tamanho do log de transações está definido como um valor máximo fixo ou o aumento automático está desabilitado, não podendo aumentar.

Erro 40552: a sessão foi encerrada devido ao uso excessivo de espaço de 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.

Embora o erro 9002 seja mais comum do que o erro 40552 na Instância Gerenciada de SQL do Azure, ambos podem ocorrer.

Para resolver o Erro 40552, tente os métodos a seguir.

  • O problema pode ocorrer em qualquer operação DML, como inserção, atualização ou exclusão. Revise a transação para evitar gravações desnecessárias. Tente reduzir o número de linhas que são operadas imediatamente implementando envio em lote ou dividindo-as em várias transações menores. Para obter mais informações, confira Como usar envio em lote para melhorar o desempenho do aplicativo.
  • O problema pode ocorrer devido a operações de recompilação de índice. Para evitar esse problema, verifique se a seguinte fórmula é verdadeira: (número de linhas que são afetadas na tabela) multiplicado por (tamanho médio do campo atualizado em bytes + 80) < 2 gigabytes (GB). No caso de tabelas grandes, considere a criação de partições e a execução da manutenção de índice somente em algumas partições da tabela. Para obter mais informações, consulte Criar tabelas e índices particionados.
  • Se você executar inserções em massa usando o utilitário bcp.exe ou a classe System.Data.SqlClient.SqlBulkCopy, 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.
  • Se você estiver recompilando um índice com a instrução ALTER INDEX, 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 ALTERAR ÍNDICE (Transact-SQL).

Próximas etapas