Diagnóstico de Crescimento Anormal do Log da tempdb no SQL Server

Você já se deparou com este cenário?

• Arquivo de log da TempDB crescendo sem parar.

• Arquivos de dados da TempDB praticamente do mesmo tamanho.

Foi exatamente isso que aconteceu em um ambiente de produção:
uma estação de trabalho travou no meio de uma consulta grande, o SQL Server continuou esperando o cliente, a sessão ficou em SUSPENDED e a tempdb foi quem pagou a conta. O arquivo de log foi crescendo… crescendo… por sorte minha rotina que monitora crescimento da TempDB alertou e iniciei o processo de investigação.

Neste post vou mostrar como diagnostiquei o problema, identifiquei a sessão e dei KILL com segurança, parando o crescimento do log da TempDB.

1. Sintomas

Veja que o arquivo de Log da TempDB estava lotado internament e crescendo, mas os arquivos de dados não estavam crescendo, como a TempDB possui o Recovery model SIMPLE, algo estava segurando o “truncar” do Log.

Para constatar este cenário utilizei o comando abaixo, que retornou ACTIVE_TRANSACTION:

SELECT  name, log_reuse_wait_desc
FROM sys.databases
WHERE database_id = 2  -- tempdb

A mensagem era clara: alguma transação ainda estava “segurando” o log da tempdb.

2. Identificando a Transação

Executando o DBCC OPENTRANS na TempDB identifiquei a sessão que estava prendendo o Log da TempDB!

Uma consulta em tabelas grandes e ORDER BY teve que utilizar a TempDB para ordenar, mas a estação de trabalho congelou no meio da execução, resultado o SQL Server estava enviando os dados para estação, ela “sumiu” e o SQL Server ficou aguardando para continuar enviando os dados… já tinha 24 horas!

Para obter mais informações da sessão utilizei a consulta abaixo:

DECLARE @SessionId INT = 774

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,
    r.status      AS request_status,
    r.command,
    DB_NAME(r.database_id) AS Banco,
    r.start_time,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id = @SessionId

3. O que aconteceu de fato?

Conversando com o usuário e com a equipe de suporte, o cenário ficou claro:

1. O usuário disparou uma consulta grande pela aplicação.

2. O SQL Server começou a processar:

   • Gerou sort na TempDB devido ao ORDER BY em grande volume de dados (o Banco tem 3TB),

   • Reservou espaço de Log na TempDB para a transação.

3. Durante o envio dos resultados para a estação, o Windows do usuário congelou.

4. O cliente parou de consumir os dados, mas o SQL Server continuou aguardando o cliente.

5. A sessão ficou em SUSPENDED com ASYNC_NETWORK_IO/CXPACKET:

   • Query não terminava,

   • Transação não dava commit nem rollback,

   • O SQL Server não pode truncar o arquivo de log da TempDB, pois partes ainda estavam associadas a uma transação em aberto, resultado foi acumulando toda a atividade que ocorreu posteriormente!

Enquanto isso:

• O log da TempDB continuava crescendo (novas operações no servidor),

• Os arquivos de dados da TempDB quase não mudavam,

• E o log_reuse_wait_desc = ACTIVE_TRANSACTION apontava para algo que as DMVs “normais” não mostravam claramente.

4. Resolvendo o problema

Um simples KILL 774 resolveu instantâneamente o problema, abortando a consulta e liberando os recursos na TempDB.

Em seguida executei DBCC SHRINKFILE para reduzir o arquivo de Log para o tamanho adequado para a atividade do servidor.

Conclusão

O caso da estação que travou no meio da consulta em tabela grande é um exemplo perfeito de como: Um simples ORDER BY em tabela volumosa, somado a um crash na estação do usuário, pode gerar crescimento anormal do log da tempdb e dor de cabeça para a empresa e o DBA.

Com algumas consultas bem escolhidas — DBCC OPENTRAN, DMVs de sessão e uma query para encontrar requests SUSPENDED de longa duração, é possível identificar rapidamente a sessão problemática, tomar a decisão de dar KILL com segurança e devolver o controle da TempDB ao ambiente.

---

Fui, mas volto com mais SQL Server em breve!

✍️ Sobre o autor
O Prof. Landry é especialista em Microsoft SQL Server desde 1999, Microsoft Trainer, Professor Universitário e criador do canal SQL Server Expert no YouTube, com conteúdo técnico semanal para DBAs e profissionais de dados.

🚀 Quer aprender mais sobre SQL Server?
👉 Me acompanhe no LinkedIn e inscreva-se no canal para não perder nenhuma dica prática!