Para Administradores de Bases de Dados (DBAs) e engenheiros de DevOps que gerem o Microsoft SQL Server, poucos alertas causam tanta ansiedade imediata como o Erro 9002: O registo de transações para a base de dados ‘X’ está cheio. Quando o registo de transações fica cheio e não consegue aumentar, a base de dados torna-se efetivamente só de leitura. Todas as operações INSERT, UPDATE e DELETE param, as transações da aplicação falham e a produção fica paralisada.
Compreender a arquitetura subjacente do registo de transações do SQL Server, diagnosticar com precisão a causa raiz e executar procedimentos de recuperação rápidos são competências críticas para manter a alta disponibilidade. Este guia abrangente explora a mecânica do registo de transações, como resolver um registo cheio numa emergência e as melhores práticas arquitetónicas para evitar que isto volte a acontecer.
Compreender a Arquitetura do Registo de Transações do SQL Server
Para resolver eficazmente um registo de transações cheio, deve primeiro compreender como o SQL Server escreve e gere os dados.
Write-Ahead Logging (WAL)
O SQL Server utiliza um protocolo de Write-Ahead Logging (WAL). Sempre que ocorre uma modificação de dados, a alteração é primeiro escrita no registo de transações na memória e, em seguida, descarregada para o ficheiro de registo físico no disco antes de as páginas de dados reais serem atualizadas nos ficheiros da base de dados (MDF/NDF). Isto garante a conformidade ACID (Atomicidade, Consistência, Isolamento, Durabilidade), assegurando que, em caso de falha, o SQL Server possa repetir (roll forward) ou anular (roll back) as transações.
Ficheiros de Registo Virtuais (VLFs) e Registo Circular
Internamente, o ficheiro de registo de transações físico (LDF) é dividido em segmentos lógicos mais pequenos chamados Ficheiros de Registo Virtuais (VLFs). O registo de transações funciona de forma circular. À medida que os registos são escritos, preenchem um VLF e passam para o seguinte.
Quando o registo chega ao fim do ficheiro físico, tenta voltar ao início. No entanto, só pode substituir um VLF se esse VLF estiver marcado como inativo. Se todos os VLFs estiverem ativos (o que significa que contêm registos de transações ainda necessários pelo SQL Server), o registo não pode recomeçar. Se o crescimento automático estiver ativado e houver espaço em disco disponível, o ficheiro físico aumenta. Se o disco estiver cheio ou o crescimento automático estiver restrito, encontrará o Erro 9002.
Truncagem de Registo vs. Redução (Shrink) de Registo
Um equívoco comum é que truncar o registo reduz o tamanho do ficheiro físico.
* Truncagem de Registo: O processo de marcar VLFs ativos como inativos, tornando o espaço disponível para reutilização. Não reduz o tamanho do ficheiro LDF no disco.
* Redução (Shrink) de Registo: O processo de reduzir fisicamente o tamanho do ficheiro LDF e devolver espaço ao sistema operativo.
No modelo de recuperação Completo (Full), a truncagem do registo apenas ocorre quando uma cópia de segurança do registo de transações é concluída com sucesso (assumindo que nenhum outro processo mantém o registo ativo).
Diagnosticar o Erro “Registo de Transações Cheio” (Erro 9002)
Quando o registo está cheio, o seu primeiro passo não deve ser adicionar espaço em disco ou reduzir ficheiros cegamente. Deve identificar por que motivo o registo não consegue truncar. O SQL Server fornece um mecanismo integrado para lhe dizer exatamente o que está a impedir a reutilização do registo através da vista de catálogo sys.databases.
Execute o seguinte comando T-SQL para identificar o estrangulamento:
SELECT
name AS DatabaseName,
recovery_model_desc AS RecoveryModel,
log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';
Também pode verificar a utilização atual do espaço dos seus registos de transações utilizando:
DBCC SQLPERF(LOGSPACE);
Estados comuns de log_reuse_wait_desc
- LOG_BACKUP: A base de dados está no modelo de recuperação Completo ou Bulk-Logged, e não foi feita uma cópia de segurança do registo de transações recentemente. Esta é a causa mais comum.
- ACTIVE_TRANSACTION: Uma transação de longa duração (por exemplo, uma reconstrução massiva de índices ou uma transação esquecida não consolidada) está a manter o registo ativo.
- REPLICATION / CDC: A Replicação Transacional ou o Change Data Capture (CDC) estão ativados, e o Log Reader Agent ainda não processou as transações.
- AVAILABILITY_REPLICA: Num Grupo de Disponibilidade AlwaysOn, uma réplica secundária está desligada ou a sincronizar demasiado lentamente, forçando a réplica primária a reter registos de transações até que sejam consolidados na secundária.
Estratégias de Recuperação Rápida: Resolver o Problema em Produção
Dependendo do log_reuse_wait_desc devolvido, a sua resposta de emergência variará. Aqui estão as estratégias de recuperação rápida para os cenários mais comuns.
Cenário 1: Cópias de Segurança de Registo em Falta ou a Falhar (LOG_BACKUP)
Se o tipo de espera for LOG_BACKUP, a solução é direta: deve fazer uma cópia de segurança do registo de transações.
BACKUP LOG [YourDatabaseName]
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn'
WITH COMPRESSION, STATS = 10;
Assim que a cópia de segurança terminar, os VLFs inativos serão truncados e o SQL Server retomará as operações normais. Se a sua unidade de cópia de segurança estiver cheia, poderá ter de fazer a cópia para uma partilha de rede temporária ou para um dispositivo nulo (altamente desencorajado, a menos que a base de dados seja facilmente reproduzível, pois quebra a cadeia de registos):
-- AVISO: Isto quebra a cadeia de registos e compromete a recuperação para um ponto no tempo.
-- Utilize apenas se for absolutamente necessário e siga imediatamente com uma cópia de segurança COMPLETA.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';
Cenário 2: Transações Ativas de Longa Duração (ACTIVE_TRANSACTION)
Se uma única transação estiver a correr há horas, impede a truncagem do registo durante todo o período. Primeiro, identifique a transação infratora:
DBCC OPENTRAN('YourDatabaseName');
Este comando devolve a transação ativa mais antiga e o seu ID de Processo de Servidor (SPID). Pode obter mais detalhes sobre o que o SPID está a fazer consultando as vistas de gestão dinâmica (DMVs):
SELECT
s.session_id,
s.login_name,
s.host_name,
r.start_time,
r.status,
r.command,
t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;
Se a transação for uma consulta maliciosa ou um processo bloqueado, poderá ter de a terminar para libertar o registo.
KILL <SPID>;
Nota: Terminar uma transação massiva irá desencadear um rollback, o que pode levar um tempo significativo e gerará temporariamente atividade de registo adicional. Não reinicie o serviço do SQL Server durante um rollback, caso contrário a base de dados entrará em modo de recuperação após o reinício.
Cenário 3: Alocação de Espaço de Emergência (Disco 100% Cheio)
Se o ficheiro LDF consumiu toda a unidade, não consegue sequer fazer uma cópia de segurança porque o SQL Server requer uma pequena quantidade de espaço de registo para gravar o próprio evento de cópia de segurança. Neste cenário, deve adicionar um ficheiro de registo secundário noutra unidade com espaço disponível.
ALTER DATABASE [YourDatabaseName]
ADD LOG FILE
(
NAME = N'YourDatabaseName_Log2',
FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
SIZE = 5GB,
MAXSIZE = 50GB,
FILEGROWTH = 1GB
);
Isto fornece imediatamente ao SQL Server espaço para respirar. Assim que a base de dados estiver online, faça uma cópia de segurança do registo de transações, esvazie o ficheiro de registo secundário e remova-o:
-- 1. Faça uma cópia de segurança do registo para truncar o registo
BACKUP LOG [YourDatabaseName] TO DISK = '...';
-- 2. Esvazie o ficheiro de registo temporário
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);
-- 3. Remova o ficheiro de registo temporário
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];
Melhores Práticas para Prevenção e Gestão do Registo de Transações
A resolução de problemas reativa é stressante e afeta os SLAs. Implementar melhores práticas arquitetónicas e operacionais proativas é essencial para a estabilidade da base de dados empresarial.
1. Implementar uma Estratégia de Cópia de Segurança Robusta e Automatizada
Se uma base de dados estiver no modelo de recuperação Completo, cópias de segurança frequentes do registo de transações são obrigatórias. Dependendo do seu Objetivo de Ponto de Recuperação (RPO) e do volume de transações, as cópias de segurança do registo devem ocorrer a cada 5 a 15 minutos.
Soluções de cópia de segurança empresariais como o CloudSave simplificam este processo significativamente. Ao integrar-se diretamente com o SQL Server via VDI (Virtual Device Interface), o CloudSave permite aos DBAs configurar cópias de segurança de registo de transações de alta frequência baseadas em políticas. Isto garante que os registos são continuamente truncados, encriptados de forma segura e armazenados fora do local ou em armazenamento em nuvem imutável, evitando o estado de espera LOG_BACKUP sem exigir tarefas complexas e personalizadas do SQL Agent.
2. Dimensionar Corretamente o Registo de Transações e Gerir VLFs
Confiar no crescimento automático para gerir o tamanho do seu registo de transações é um anti-padrão perigoso. As operações de crescimento automático são dispendiosas e pausam o processamento de transações enquanto o disco é inicializado com zeros (a menos que a Inicialização Instantânea de Ficheiros esteja ativada, o que não se aplica a ficheiros de registo).
Além disso, crescimentos automáticos frequentes e pequenos (por exemplo, aumentar 10% ou 50MB de cada vez) levam à fragmentação de VLF. Um registo de transações com milhares de pequenos VLFs degradará gravemente os tempos de arranque da base de dados, o desempenho das cópias de segurança e a latência da replicação.
- Pré-dimensione o registo: Analise as suas maiores operações de manutenção (como reconstruções de índices) e pré-dimensione o ficheiro LDF para as acomodar sem crescer.
- Defina um crescimento automático fixo: Altere o crescimento automático de uma percentagem para um tamanho fixo (por exemplo, 1GB ou 5GB) para garantir que os VLFs são criados com um tamanho saudável.
Pode verificar a sua contagem de VLF utilizando a seguinte consulta (para SQL Server 2017+):
SELECT
db_name(database_id) AS DatabaseName,
COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));
Se a sua contagem de VLF for superior a 500, considere esperar por um período de pouca atividade, reduzir o registo para um tamanho mínimo e aumentá-lo manualmente de volta ao tamanho necessário em grandes blocos.
3. Otimizar Operações de Manutenção de Índices
As reconstruções de índices são operações totalmente registadas, mesmo no modelo de recuperação Bulk-Logged (dependendo do tipo de índice). Reconstruir um índice de 500GB gerará pelo menos 500GB de registos de transações.
Para mitigar o inchaço do registo durante a manutenção:
* Utilize SORT_IN_TEMPDB = ON ao reconstruir índices. Isto descarrega a fase de ordenação para a TempDB, reduzindo a carga no registo de transações da base de dados do utilizador.
* Mude de reconstruções de índices para reorganizações de índices sempre que possível, uma vez que as reorganizações são mais eficientes em termos de registo e podem ser interrompidas sem anular toda a operação.
* Agrupe grandes operações de DELETE ou UPDATE. Em vez de eliminar 10 milhões de linhas numa transação, elimine-as em blocos de 50.000, consolidando e permitindo que as cópias de segurança do registo truncuem o registo entre blocos.
4. Monitorizar Topologias de Alta Disponibilidade e Replicação
Em Grupos de Disponibilidade AlwaysOn, a réplica primária não pode truncar o seu registo até que os registos de transações tenham sido consolidados em todas as réplicas secundárias síncronas e assíncronas.
Se uma réplica secundária ficar offline, ou se a largura de banda da rede não conseguir acompanhar a taxa de geração de transações da primária, a fila de envio da primária crescerá e o registo ficará cheio (tipo de espera AVAILABILITY_REPLICA).
Implemente uma monitorização robusta para o contador de desempenho SQLServer:Replica > Log Send Queue. Se uma réplica secundária for permanentemente perdida, deve removê-la do Grupo de Disponibilidade ou suspender o movimento de dados para permitir que o registo primário seja truncado.
Conclusão
Encontrar um registo de transações cheio é um rito de passagem para administradores de bases de dados, mas não tem de resultar em tempo de inatividade prolongado. Ao compreender a mecânica do Write-Ahead Logging e dos VLFs, pode diagnosticar rapidamente a causa raiz utilizando sys.databases e aplicar a estratégia de recuperação rápida correta.
A estabilidade a longo prazo depende de se afastar de correções reativas. Pré-dimensionar os seus ficheiros de registo, otimizar as rotinas de manutenção e utilizar plataformas de cópia de segurança de nível empresarial como o CloudSave para aplicar horários de cópia de segurança de registo rigorosos e automatizados garantirá que os seus registos de transações permaneçam saudáveis, truncados e prontos para suportar cargas de trabalho de produção de alto débito.
