Loading…

Casos do Dia a Dia: Monitorando o tamanho do arquivo MDF do Tempdb e sua Version Store

tempdb

Olá Pessoal,

Em um dos meus clientes onde realizo um suporte e monitoramento mensal, tenho um problema de crescimento do TEMPDB que pode levar até a uma falta de espaço em disco do servidor.

Esse cliente tem habilitada na base de produção a opção Read Committed Snapshot. Você pode conferir isso com a query abaixo:

select name,is_read_committed_snapshot_on
from sys.databases
where is_read_committed_snapshot_on = 1

Com essa opção habilitada na base o SQL Server mantém um versionamento de dados no tempdb para reduzir os locks que ocorrem com consultas. As consultas vão retornar o último dado commitado se alguém tiver alterando esse dado.

O problema acontece quando a aplicação abre algumas conexões que seguram o tempdb, que usa esse versionamento, e não fecha adequadamente essas conexões. Com isso, o espaço do tempdb utilizado pela version store vira uma bola de neve e cresce sem parar.

Enquanto o pessoal da aplicação não resolve o problema, esse alerta me ajuda a identificar quando esse problema acontece e já me indica as conexões abertas a mais de uma hora para que no próprio e-mail eu já consiga identificar quais conexões matar.

Segue abaixo o e-mail enviado pelo Alerta:

postalertaversionstoretempdb

Nesse e-mail posso ver que o TEMPDB tem 86 GB de tamanho, sendo 25 GB ainda livre e 60 GB utilizado só pela parte da Version Store.

Uma opção seria matar o processo automaticamente, mas ainda não precisei chegar nesse nível.

Após finalizar essa conexão que estava segurando o TEMPDB, pouco tempo depois já recebo o CLEAR abaixo:

postalertaversionstoretempdb_clear

Agora podemos ver que o espaço não alocado do TEMPDB voltou para 85 GB e que a version Store agora está usando apenas 697 MB.

Segue o script para criar esse alerta: Download Script

Esse script vai criar uma tabela chamada Alerta para controlar o Alerta e o Clear.

E também vai criar uma procedure chamada stpAlerta_Tamanho_MDF_Tempdb que fará a validação. Você deve colocar essa procedure em um job para rodar a cada 5 minutos pelo menos.

Você também terá que fazer uma alteração logo no início da proc, colocando o seu profile e os e-mails que vão receber os alertas separados por ponto e vírgula:

”  set @ProfileSQL = ‘Seu Profile’     set @Emails = ‘Seu Email;Outro Email’  “

OBS: Para quem usa o SQL Server 2008 R2 ou inferior, você não terá a informação de Open_transaction_Count, então terá que alterar essa query abaixo no código original:

INSERT INTO #Alerta_Tamanho_MDF_Tempdb_Conexoes
 SELECT session_id, convert(varchar(20),login_time,120) AS login_time, login_name, host_name, 'SQL Server 2012 ou superior' open_transaction_Count, status, cpu_time,
   total_elapsed_time, reads, writes, logical_reads
 FROM sys.dm_exec_sessions
 WHERE session_id > 50
   --and open_transaction_Count > 0
   and dateadd(hour,-@Tempo_Conexoes_Hs,getdate()) > login_time

Isso não vai te atrapalhar a identificar o problema no tempdb, você só terá uma informação a menos no e-mail.

Gostou dessa dica?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

No meu Treinamento de Tarefas do Dia a Dia de um DBA tenho outros alertas para te ajudar no monitoramento do seu SQL Server.

Até a próxima.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

3 thoughts on “Casos do Dia a Dia: Monitorando o tamanho do arquivo MDF do Tempdb e sua Version Store

  1. Olá, Fabrício!

    Excelente post.
    Inclusive já tive um problema semelhante a este com crescimento enlouquecido do TempDB, mas por outros motivos.
    Tentei utilizar o seu script, mas na sys.dm_exec_sessions não existe a coluna “open_transaction_Count”. Pelo menos no SQL 2008 R2 onde tentei. É de alguma versão mais atual?

    Obrigado!

    1. Obrigado pelo feedback Laercio.

      Acabei de colocar uma observação no post. Você terá que ignorar essa coluna. Altera o código para esse abaixo:

      INSERT INTO #Alerta_Tamanho_MDF_Tempdb_Conexoes
      SELECT session_id, convert(varchar(20),login_time,120) AS login_time, login_name, host_name, ‘SQL Server 2012 ou superior’ open_transaction_Count, status, cpu_time,
      total_elapsed_time, reads, writes, logical_reads
      FROM sys.dm_exec_sessions
      WHERE session_id > 50
      –and open_transaction_Count > 0 –comentado porque não existe no 2008
      and dateadd(hour,-@Tempo_Conexoes_Hs,getdate()) > login_time

Deixe uma resposta

%d blogueiros gostam disto: