SQL Server: log de transação no mesmo disco dos dados

Quando o arquivo de log de transação (LDF) divide o mesmo disco físico com o arquivo de dados (MDF), a latência de escrita do log sobe mesmo que as métricas "médias" do disco pareçam administráveis — porque o padrão de escrita sequencial e constante do log fica atrás, na mesma fila de I/O, da leitura aleatória e pesada do arquivo de dados. A correção mais barata e menos arriscada costuma ser isolar só o log em outro disco, sem tocar no arquivo de dados.

Foi esse o quadro encontrado recentemente numa aplicação de produção crítica rodando SQL Server numa VM do Azure: um banco de alguns terabytes, escrita constante, e um disco que segundo o painel de infraestrutura "não estava tão ruim assim".

O sintoma que não aparecia no lugar óbvio

O disco em questão era um Standard SSD de 8 TB, hospedando ao mesmo tempo o arquivo de dados principal (6,84 TB) e o log de transação (50 GB) do mesmo banco. Em termos de espaço, sobrava folga suficiente para não disparar alerta de capacidade. Em termos de IOPS consumido, a média ficava em torno de 81%, com picos batendo 99% — alto, mas não o tipo de número que costuma acender um alarme vermelho isolado num dashboard genérico de infraestrutura.

O problema não estava na média. Estava na composição do tráfego. O arquivo de dados gera leitura aleatória, muitas vezes em rajadas, conforme a aplicação consulta e grava linhas espalhadas pelo banco. O log de transação gera outro padrão completamente diferente: escrita sequencial, constante, e sensível a latência — porque cada transação da aplicação só é confirmada depois que o log correspondente é gravado em disco. Colocar os dois padrões no mesmo disco físico significa que a escrita fina e sequencial do log espera na mesma fila que a leitura pesada e irregular dos dados.

O resultado medido: latência média do disco em 71,91 ms, queue depth médio de 120 (com picos acima de 400) — e, o número que mais importa aqui, latência de escrita do próprio log em 148,14 ms. Para um arquivo de log, isso é alto o bastante para arrastar o tempo de resposta de qualquer transação de escrita da aplicação, mesmo em queries individualmente simples.

Por que mover só o log, e não o arquivo de dados

A tentação numa situação dessas é pensar em migrar o arquivo de dados inteiro para um disco mais rápido. Para um banco de quase 7 TB, isso significa uma operação longa, de alto risco, com uma janela de indisponibilidade proporcional ao volume movido — e um retorno que não ataca a causa raiz da contenção, porque o padrão de leitura aleatória do MDF continuaria concorrendo com o log dentro do mesmo disco de destino.

A alternativa mais barata era isolar apenas o log. Um disco Premium SSD (P30) já existente na mesma VM, usado para hospedar logs de outros bancos, tinha apenas 1,3% de utilização de IOPS e mais de 800 GB livres — folga suficiente para receber mais 50 GB sem risco de repetir o mesmo problema em outro lugar. Mover um arquivo de 50 GB é uma operação de minutos, não de horas, e o arquivo de dados de 6,84 TB permanece exatamente onde estava, sem risco adicional.

Essa é a lógica que costuma valer em contenção de I/O: identificar qual dos dois padrões de escrita — sequencial e constante (log) ou aleatório e pesado (dados) — está sofrendo mais com a disputa, e mover o menor e mais barato dos dois, não necessariamente o mais óbvio.

A execução: uma janela pequena para um risco pequeno

O procedimento em si é simples e bem documentado no próprio SQL Server: registrar o novo caminho do arquivo de log via ALTER DATABASE ... MODIFY FILE, colocar o banco OFFLINE, mover o arquivo fisicamente para o novo disco, e colocar o banco ONLINE de novo apontando para o caminho atualizado. A parte que exige cuidado não é o comando — é o que cerca ele.

Antes da janela: backup FULL validado com RESTORE VERIFYONLY, confirmação de que não havia backup, restore ou manutenção em execução, e drenagem das conexões ativas no banco. Durante a movimentação do arquivo, a cópia optou por preservar o original no disco antigo em vez de mover e apagar — uma rede de segurança barata: se algo desse errado depois do ONLINE, o rollback mais rápido era simplesmente repontar o metadado de volta para o caminho antigo, sem precisar restaurar backup.

A janela aprovada para a mudança era de 30 a 45 minutos de indisponibilidade do banco. A indisponibilidade real ficou em cerca de 8 minutos — do OFFLINE ao banco respondendo de novo a uma consulta real da aplicação.

O resultado

A queda de 148 ms para 3,14 ms de latência de escrita do log — uma redução de quase 98% — ficou próxima do baseline do próprio disco de destino, medido antes da mudança em 1,83 ms. Ou seja: o log deixou de ser gargalo praticamente por completo, sem exigir nenhuma alteração no arquivo de dados nem investimento em disco novo. O recurso já existia, só estava sendo usado por outra coisa.

O achado que quase passou despercebido

A validação pós-mudança quase gerou um falso alarme. Ao medir a latência de escrita do arquivo de dados no mesmo disco antigo, o número apareceu bem alto — o tipo de resultado que faria parecer que a mudança tinha piorado algo. A investigação mostrou outra causa: um job de manutenção antigo, agendado para rodar DBCC SHRINKFILE periodicamente no arquivo de dados tentando liberar espaço, estava concorrendo justamente durante a janela de validação e contaminando a medição.

SHRINKFILE recorrente contra um arquivo de vários terabytes é, por si só, um anti-padrão conhecido: gera I/O pesado e aleatório a cada execução e fragmenta índices, criando um ciclo de "encolhe → a aplicação recresce → encolhe de novo" que nunca resolve o problema de espaço em disco de forma definitiva — e pode ter sido, inclusive, um contribuinte silencioso da própria saturação de I/O que motivou a mudança original. Isso não derrubou nem colocou em risco a mudança do log, mas virou um item de acompanhamento separado: revisar a necessidade daquele job antes de tratá-lo como normal.

O que ficou de fora dessa mudança, de propósito

Duas decisões deliberadas mantiveram o escopo pequeno e o risco baixo. A primeira foi não incluir, na mesma janela, um ajuste de configuração de cache do disco de destino — mudança relacionada, mas independente, deixada para uma janela própria, para não acumular duas variáveis de risco na mesma mudança. A segunda foi abortar uma verificação completa de integridade (DBCC CHECKDB) do banco: para um arquivo de quase 7 TB, o tempo estimado passava de 22 horas, o que levaria a checagem para dentro do horário comercial, competindo por I/O com a própria aplicação — exatamente o problema que a mudança buscava resolver. A checagem completa foi replanejada para uma janela dedicada, com verificação por tabela em rodízio ao longo de várias noites, em vez de uma checagem monolítica.

Nenhuma das duas decisões compromete a mudança feita. Ambas evitam transformar uma correção pontual e de baixo risco numa operação mais longa e mais arriscada do que precisava ser.

Se esse padrão parece familiar

Log de transação e arquivo de dados dividindo o mesmo disco é um padrão comum — muitas vezes herdado de um provisionamento inicial que nunca foi revisado depois que o banco cresceu. E como o sintoma aparece como latência de escrita "meio alta" em vez de um erro claro, costuma ficar meses sem diagnóstico, sendo compensado com mais CPU ou mais memória em vez de resolvido na causa. Vale olhar também como isso se conecta com quais métricas de banco realmente antecipam um incidente — latência de escrita do log é uma das que mais raramente aparece num dashboard genérico, mas é uma das que mais cedo denuncia esse tipo de contenção.

Para quem quer descobrir se esse padrão existe no próprio ambiente, a H1 Data oferece um diagnóstico gratuito do ambiente SQL Server: levantamento de onde os arquivos de log e dados estão hospedados, dos indicadores de I/O que já denunciam contenção, e do que pode ser corrigido com uma janela pequena, sem mexer no arquivo de dados.