#9 - TEMPDB

Hoje um dos servidores de banco reportou problema de falta de espaço na unidade em que fica toda a instalação do SQL SERVER, pra ter uma idéia fizemos a separação dos arquivos do sql da seguinte maneira:

  F: - Instalação do SQL e Bancos Padrões (Master, Tempdb, Msdb, Model)

  G: - Arquivos de Log (ldf).

  H: - Arquivos de Dados (mdf).

Resumindo de uma forma mais clara cada unidade é um disco do storage. Aparentemente estaria tudo certo se não fossem dois detalhes o Banco Tempdb teve um crescimento muito grande durante a noite consumindo todo o espaço disponível da unidade F: , o outro detalhe foi que sem perceber foram criados bancos na unidade F:, o que não é correto.

O primeiro passo que fizemos foi remover todos os bancos que estavam na unidade errada e alocando os arquivos de log e dados nos devidos locais. Até ai tudo muito fácil.

O Shrink em um banco não é um procedimento muito recomendado mas no caso como precisávamos liberar espaço no servidor por causa do grande crescimento do tempdb. 

A primeira solução seria reiniciar a instância do SQL, esse procedimento recria a tempdb, se você desejar pode colocar uma limitação de crescimento do tamanho do banco. 

A segunda solução seria encontrar quais querys estão ocupando espaço no banco Tempdb, encontrei há um tempo atrás um artigo do Fabrício Lima: Querys do Dia a Dia: Como encontrar as conexões que mais ocupam espaço no Tempdb usei a seguinte query para ver quem estava ocupando o Tempdb

SELECT A.session_id,B.host_name, B.Login_Name ,(user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 as TotalalocadoMB,

D.Text

FROM sys.dm_db_session_space_usage A

JOIN sys.dm_exec_sessions B  ON A.session_id = B.session_id

JOIN sys.dm_exec_connections C ON C.session_id = B.session_id

CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) As D

WHERE A.session_id > 50

and (user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 > 100 — Ocupam mais de 100 MB

ORDER BY totalalocadoMB desc

COMPUTE sum((user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128)

O interessante é que mostra todos os detalhes do que está ocupando espaço, deletei todas as SPIDs que estavam consumindo uma grande espaço. Infelizmente o meu caso também não foi suficiente porque o banco tinha algum processo travado que não foi liberado. 

Então utilizei as recomendações do Pradeep Adiga que você pode ler todo o artigo na integra aqui. 

Tomei a liberdade de traduzir uma parte do artigo do Pradeep Adiga, segue um pequeno resumo dos passos executados , mas no meu caso a solução foi reiniciar a instância do sql.

DBCC SHRINKFILE ('tempdev', 1024)

O próximo passo óbvio seria para verificar qualquer transação aberta no tempdb.

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

Nenhuma transação aberta! Tudo bem, qualquer processo de exploração bloqueios em tempdb?

select * from sys.dm_tran_locks where resource_database_id= 2

Nenhum bloqueio! Então me deparei com este artigo no MSDN. Havia uma menção de DMV sys.dm_db_session_space_usage que ajuda a controlar o número de alocação de página e desalocação de cada sessão na instância. Daí tentei a minha sorte com esta consulta.

select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0

Qualquer sessão usaria tempdb para a criação de alguns objetos temporários

SELECT * FROM tempdb..sys.all_objects where is_ms_shipped = 0

A única maneira de livrar-se desses objetos em cache é limpar o cache procedure. Não é uma boa idéia limpar o cache de Procedure, uma vez que vai obrigar as stored procedures para ser recompilados e, consequentemente, afectar negativamente o desempenho.

DBCC FREEPROCCACHE