Casos do Dia a Dia: Monitorando o tamanho do arquivo MDF do Tempdb e sua Version Store
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:
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:
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:
|
1 2 3 4 5 6 7 |
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
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!
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
Eu que agradeço a atenção, Fabrício!
Grande abraço!