MSSQL – TEMPDB – O que fazer quando receber um alarme por falta de espaço

   Inicio esse post com uma parte do artigo do MSDN explicando o que é uma TEMPDB.

   O banco de dados do sistema tempdb é um recurso global disponível a todos os usuários conectados a uma instância de SQL Server e é usado para manter o seguinte:
  • Os objetos de usuário temporários criados explicitamente como: tabelas temporárias globais ou locais, procedimentos armazenados temporários, variáveis de tabela ou cursores.
  • Objetos internos criados por Mecanismo de Banco de Dados do SQL Server, por exemplo, tabelas de trabalho para armazenar resultados intermediários para spool ou classificação.
  • Versões de linha geradas por transações de modificação de dados em um banco de dados que usa a leitura de confirmados usando transações de isolação de controle de versão de linha ou isolação de instantâneo.
  • As versões de linhas geradas por meio de transações de modificação de dados para recursos como: operações de índice on-line, vários conjuntos de resultados ativos (MARS) e gatilhos AFTER.
   As operações em tempdb são registradas minimamente. Isso permite que transações sejam revertidas. tempdb é recriado cada vez que SQL Server é iniciado, de modo que o sistema sempre começa com uma cópia limpa do banco de dados. As tabelas temporárias e procedimentos armazenados são descartados automaticamente ou desconectados e nenhuma conexão fica ativa quando o sistema é desligado. Portanto, nunca há nada em tempdb a ser gravado de uma sessão de SQL Server para outra. As operações de backup e restauração não são permitidas em tempdb.

  Muitas vezes em determinados momentos, você DBA pode se deparar com o um alarme que o database TEMPDB, que é um recurso de armazenamento global temporário esta próximo de atingir 100% de ocupação.
Isso é um problema, mas que pode ser resolvido com algumas ações:

Passo 1 – Identificar a query ofensora

     A melhor forma de identificar é executando uma query de monitoramento que retorne as informações que você precisa para a analise. Para isso execute no console:

select top 10
t1.session_id,
t1.request_id,
t3.hostname,
t3.loginame,
t3.program_name,
db_name (t3.dbid) as dbname,
t1.task_alloc  * (8.0/1024.0) as Alocado_MB, --qtd de paginas
t1.task_dealloc  * (8.0/1024.0)as Desalocado_MB, --qtd de paginas

    (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1
              THEN LEN(CONVERT(nvarchar(max),text)) * 2
                   ELSE statement_end_offset
              END - t2.statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(t2.sql_handle)) AS query_text,
(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

from      (Select session_id, request_id,
sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
       from sys.dm_db_task_space_usage
       group by session_id, request_id) as t1,
       sys.dm_exec_requests as t2,
       sys.sysprocesses as t3
where
t3.loginame <> '' and
t1.session_id = t2.session_id and
(t1.request_id = t2.request_id) and
t1.session_id = t3.spid and
      t1.session_id > 50
order by t1.task_alloc DESC

   O importante nesse caso é analisar o que essa query esta fazendo e o quanto está alocando no momento do pico.
Aguarde alguns segundos e execute novamente, e nesse caso, analise o próximo campo que será o quanto esta liberando de espaço. O que isso quer dizer:
Na verdade se a query esta usando muito espaço, mas ao executar novamente verifica-se que o valor livre esta crescendo, quer dizer que essa query já fez o que precisava e gradativamente esta devolvendo esse espaço a TEMPDB. Nesse caso somente aguarde que a TEMPDB será liberada em breve e o alarme vai parar. O ultimo campo não exibido aqui é o plano de execução.
Caso a query continue crescendo e consumindo um espaço exorbitante da TEMPDB a ação correta a tomar é a seguinte:

Passo 2 – Analizar as configurações da TEMPDB

   A TEMPDB está setada com um valor X pré-supondo que é o necessário para o armazenamento necessário das transações.
Para verificar essas informações clique com o botão direito no database TEMPDB > Properties, como mostra a figura abaixo.

   Clique em files, como mostra a figura abaixo:

   O arquivo lógico que precisamos analisar chama-se TEMPLOG, verifique para qual caminho fisico que ele esta apontando, que nesse caso é o *E:* (Não exibido aqui, mas fica ao lado da imagem abaixo) e clique  no botão ao lado das informações de tamanho como mostra a figura a seguir

Nesse momento será exibidos os parâmetros de crescimento do templog, como mostra a figura aseguir.
Caso haja um crescimento de informações, ela irá crescer gradativamente, desde que o parâmetro AutoGrowth esteja setado.
No parâmetro File Growth está definido como será esse crescimento, nesse caso a cada 1GB.
E  por fim analisar o parâmetro Maximum File Size, que define o valor máximo total que esse database pode crescer fisicamente no disco, nesse caso 20GB.

Antes de alterar qualquer parâmetro é importante saber o quanto espaço temos livre no disco em que o database esta armazenado, como vimos anteriormente.
Caso o disco tenha espaço aumente o valor máximo, gradativamente a cada 10GB, até que seja sanado o problema. Depois, quando tudo terminar volte o valor, pois essa é apenas uma medida paliativa.

Também é possivel fazer esse procedimento via código:

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', MAXSIZE = 30720000KB , FILEGROWTH = 102400KB )
GO

Passo 3 – Estudando a query ofensiva e sugerindo tunning para melhoria.

Aumentar tamanho de disco, na verdade é uma medida de emergência.
O que deve ser feito com calma é um estudo da query ofensiva e resolver definitivamente o problema. Para isso é necessário analizar o plano de execução dessa query para entender onde esta o auto consumo do banco.
Para isso clique no link que o aparece no resultado da execução da query de monitoramento ( query mostrada no inicio desse documento ).
Será exibido uma pagina em xml com o plano de execução, como mostra a figura a seguir:

    Esse é o plano de execução da query ofensiva. O SQL Server automaticamente detecta onde esta o problema e sugere uma ação de “tunning” para melhorar a query. Essa sugestão é mostrada no cabeçalho da página como mostra o topo da imagem.
Ao executar a query de monitoramento sabemos qual host, login e programa esta executando a tal query. Entre em contato com o departamento responsável por essa query notificando o auto consumo.

Passo 4 – Matando o SQL Ofensor !!!

Quando nada resolver … é só matar essa query.
Para isso é necessário primeiro descobri o SPID dessa query com o comando a seguir.

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

Agora é só matar o processo com o comando:

kill SPID

Passo 5 – Algumas referência.


Artigo Completo MSDN Sobre TEMPDB

http://www.red-gate.com/specials/Grant.htm

Juarez Silva, DBA Senior  que contribuiu com o desenvolvimento do script de monitoração.

Espero ter ajudado um pouco …

Abraços

Kenia Milene

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s