Artigo
09/28/2010

Solucionando problemas de espaço insuficiente em disco em tempdb

Este tópico oferece procedimentos e recomendações para ajudá-lo a diagnosticar e solucionar problemas causados por espaço em disco insuficiente no banco de dados tempdb. Ficar sem espaço em disco em tempdb pode causar interrupções significativas no ambiente de produção do SQL Server e pode impedir que aplicativos que estão em execução concluam as operações.

Requisitos de espaço para tempdb

O banco de dados do sistema tempdb é um recurso global disponível a todos os usuários conectados a uma instância do SQL Server. O banco de dados tempdb é utilizado para armazenar os seguintes objetos: objetos do usuário, objetos internos e armazenamentos de versão.

Você pode utilizar a exibição de gerenciamento dinâmico sys.dm_db_file_space_usage para monitorar o espaço em disco utilizado pelos objetos de usuário, objetos internos e armazenamentos de versão nos arquivos tempdb. Além disso, para monitorar a atividade de alocação ou desalocação de página em tempdb no nível da sessão ou tarefa, você pode utilizar as exibições de gerenciamento dinâmico sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Essas exibições podem ser utilizadas para identificar consultas grandes, tabelas temporárias ou variáveis de tabela que estão utilizando muito espaço em disco de tempdb.

Diagnosticando problemas de espaço em disco tempdb

A tabela seguinte lista mensagens de erro que indicam espaço em disco insuficiente no banco de dados tempdb. Esses erros podem ser localizados no log de erros do SQL Server e também podem ser retornados a qualquer aplicativo em execução.

Erro	É gerado quando
1101 ou 1105	Qualquer sessão deve alocar espaço em tempdb.
3959	O armazenamento de versão está cheio. Este erro normalmente aparece depois de um erro 1105 ou 1101 no log.
3967	O armazenamento de versão é forçado a ser reduzido porque tempdb está cheio.
3958 ou 3966	Uma transação não pode encontrar o registro de versão necessário em tempdb.

Problemas de espaço em disco tempdb também são indicados quando o banco de dados é definido como aumento automático e o tamanho do banco de dados está aumentando rapidamente.

Monitorando espaço em disco tempdb

Os exemplos a seguir mostram como determinar o volume de espaço disponível em tempdb e o espaço usado pelo armazenamento de versão e objetos internos e do usuário.

Determinando a quantidade de espaço livre em tempdb

A consulta a seguir retorna o número total de páginas livres e o espaço livre total em megabytes (MB) disponível em todos os arquivos em tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Determinando o volume de espaço usado pelo armazenamento de versão

A consulta a seguir retorna o número total de páginas usadas pelo armazenamento de versão e o espaço total em MB usado pelo armazenamento de versão em tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

Determinando a transação mais longa em execução

Se o armazenamento de versão estiver usando muito espaço em tempdb, você deverá determinar qual é a transação mais longa em execução. Use esta consulta para relacionar as transações ativas em ordem, pela transação mais longa em execução.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Uma transação longa em execução que não estiver relacionada a uma operação de índice online requer um grande armazenamento de versão. Esse armazenamento de versão mantém todas as versões geradas desde o início da transação. Transações de construção de índice online podem levar muito tempo para terminar, mas um armazenamento de versão separado dedicado a operações de índice online é usado. Portanto, essas operações não impedem a remoção das versões de outras transações. Para obter mais informações, consulte Uso do recurso de controle de versão de linha.

Determinando o volume de espaço usado por objetos internos

A consulta a seguir retorna o número total de páginas usadas por objetos internos e o espaço total em MB usado por objetos internos em tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Determinando o volume de espaço usado por objetos do usuário

A consulta a seguir retorna o número total de páginas usadas por objetos do usuário e o espaço total em MB usado por objetos internos em tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Determinando o volume total de espaço (livre e usado)

A consulta a seguir retorna o volume total de disco usado por todos os arquivos em tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

Monitorando espaço usado por consultas

Um dos tipos mais comuns de problemas de uso de espaço tempdb está associado a grandes consultas que usam um grande volume de espaço. Geralmente, esse espaço é usado para objetos internos, como tabelas de trabalho ou arquivos de trabalho. Embora a monitoração do espaço usado por objetos internos indique quanto espaço está sendo usado, ela não identifica diretamente a consulta que está usando esse espaço.

Os métodos a seguir ajudam a identificar as consultas que estão usando mais espaço tempdb. O primeiro método examina dados do nível de lote e requer menos dados que o segundo método. O segundo método pode ser usado para identificar a consulta específica, tabela temporária ou variável de tabela que estão consumindo o espaço em disco, mas mais dados devem ser coletados para obter a resposta.

Método 1: Informações no nível de lote

Se a solicitação de lote tiver apenas algumas consultas, e só uma delas for uma consulta complexa, essas informações costumam ser suficientes para saber qual lote está consumindo o espaço em vez da consulta específica.

Para continuar com esse método, um trabalho do SQL Server Agent deve ser criado para sondar as exibições de gerenciamento dinâmico sys.dm_db_session_space_usage e sys.dm_db_task_space_usage usando um intervalo de sondagem de alguns minutos. Um intervalo de sondagem de três minutos é usado no exemplo a seguir. Você deve sondar ambas as exibições porque sys.dm_db_session_space_usage não inclui a atividade de alocação da tarefa ativa atual. A comparação da diferença entre as páginas alocadas em dois intervalos de tempo permite calcular quantas páginas são alocadas entre os intervalos.

Os exemplos a seguir mostram as consultas necessárias para o trabalho do SQL Server Agent.

A. Obtendo o espaço consumido por objetos internos em todos os trabalhos em execução atualmente em cada sessão.

O exemplo a seguir cria a exibição all_task_usage. Quando consultada, a exibição retorna o espaço total utilizado por objetos internos em todos os trabalhos atuais em execução em tempdb.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

A. Obtendo o espaço consumido por objetos internos na sessão atual tanto para trabalhos em execução como para trabalhos concluídos.

O exemplo a seguir cria a exibição all_session_usage. Quando consultada, a exibição retorna espaço usado por todos os objetos internos em execução e os trabalhos concluídos em tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Suponha que quando essas exibições são consultadas em um intervalo de três minutos, os conjuntos de resultados trazem as informações a seguir.

Às 17:00, a sessão 71 alocou 100 páginas e desalocou 100 páginas desde o início da sessão.
Às 17:03, a sessão 71 alocou 20100 páginas e desalocou 100 páginas desde o início da sessão.

Ao analisar essas informações, você pode dizer que entre as duas medidas a sessão alocou 20.000 páginas para objetos internos e não desalocou nenhuma página. Isso indica um problema potencial.

Observação
Como administrador do banco de dados, você pode decidir sondar com mais freqüência do que três minutos. Porém, se uma consulta for executada em menos de três minutos, ela provavelmente não consumirá um volume significativo de espaço em tempdb.

Para determinar o lote que está sendo executado nesse período, use o SQL Server Profiler para capturar as classes de evento RPC:Completed e SQL:BatchCompleted.

Uma alternativa para usar o SQL Server Profiler é executar DBCC INPUTBUFFER uma vez a cada três minutos para todas as sessões, como mostrado no exemplo a seguir.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Método 2: Informações no nível de consulta

Às vezes só de olhar o buffer de entrada ou o evento SQL Server ProfilerSQL:BatchCompleted sem sempre informa qual consulta está usando a maior parte do espaço em disco em tempdb. Os métodos a seguir podem ser usados para encontrar essa resposta, mas esses métodos exigem uma coleta de dados maior do que os procedimentos definidos no Método 1.

Para continuar com esse método, configure um trabalho do SQL Server Agent que faça sondagem na exibição de gerenciamento dinâmico sys.dm_db_task_space_usage. O intervalo de sondagem deve ser curto, uma vez por minuto, comparado ao Método 1. Esse intervalo é curto porque sys.dm_db_task_space_usage não retornará dados se a consulta (trabalho) não estiver em execução no momento.

Na consulta de sondagem, a exibição definida na visualização de gerenciamento dinâmico sys.dm_db_task_space_usage está unida a sys.dm_exec_requests para retornar as colunas sql_handle, statement_start_offset, statement_end_offset e plan_handle.

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

Se o plano de consulta estiver em cache, você poderá recuperar o texto Transact-SQL da consulta e o plano de execução de consulta no formato Plano de execução XML a qualquer momento. Para obter o texto Transact-SQL da consulta que é executada, use o valor sql_handle e a função de gerenciamento dinâmicosys.dm_exec_sql_text. Para obter a execução do plano de consulta, use o valor plan_handle e a função de gerenciamento dinâmico sys.dm_exec_query_plan.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

Se o plano de consulta não estiver em cache, você poderá usar um dos métodos a seguir para obter o texto Transact-SQL da consulta e o plano de execução de consulta.

A. Usando o método de sondagem

Faça sondagem na exibição all_query_usage e execute a seguinte consulta para obter o texto de consulta:

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Como sql_handle deve ser exclusivo para cada lote individual, você não precisa salvar entradas sql_handle duplicadas.

Para salvar o tratamento do plano e o plano XML, execute a consulta a seguir.

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. Usando eventos do SQL Server Profiler

Como alternativa para sondagens das funções sys.dm_exec_sql_text e sys.dm_exec_query_plan , você pode usar eventos do SQL Server Profiler. Há eventos do profiler que podem ser usados para capturar o plano de consulta e o texto de consulta gerado. Por exemplo, o Evento 165 retorna estatísticas de desempenho para rastreamento, texto de SQL, planos de consulta e estatísticas de consulta.

Monitorando espaço usado por tabelas temporárias e variáveis de tabela

Você pode usar uma abordagem similar às consultas de sondagem para monitorar o espaço usado por tabelas temporárias e variáveis temporárias. Aplicativos que adquirem uma grande quantidade de dados de usuário dentro de tabelas temporárias ou de variáveis temporárias podem causar problemas de uso de espaço em tempdb. Essas tabelas ou variáveis pertencem aos objetos do usuário. Você pode usar as colunas user_objects_alloc_page_count e user_objects_dealloc_page_count na exibição de gerenciamento dinâmico sys.dm_db_session_space_usage e seguir os métodos descritos anteriormente.

Monitorando alocação e desalocação de página por sessão

A tabela a seguir mostra os resultados retornados pelas exibições de gerenciamento dinâmico sys.dm_db_file_space_usage, sys.dm_db_session_space_usage e sys.dm_db_task_space_usage de uma sessão especificada. Cada linha representa uma atividade de alocação ou desalocação em tempdb para uma sessão especificada. A atividade é listada na coluna Event. As colunas restantes mostram os valores que seriam retornados nas colunas das exibições de gerenciamento dinâmico.

Para esse cenário, suponha que o banco de dados tempdb é iniciado com 872 páginas em extensões não alocadas, e 100 páginas em extensões reservadas de objeto do usuário. A sessão aloca 10 páginas para uma tabela de usuário, e depois desaloca todas elas. As 8 primeiras páginas estão em extensão mista. As 2 páginas restantes estão em extensão uniforme.

Evento	coluna dm_db_file_space_usage unallocated_extent_page_count	coluna dm_db_file_space_usage user_object_reserved_page_count	coluna dm_db_session_space_usage and dm_db_task_space_usage user_object_alloc_page_count	coluna dm_db_session_space_usage and dm_db_task_space_usage user_object_dealloc_page_count
Iniciar	872	100	0	0
Aloque a página 1 da extensão mista existente	872	100	1	0
Aloque as páginas de 2 a 8: consumindo uma nova extensão mista	864	80	8	0
Aloque a página 9: consumindo uma nova extensão uniforme	856	108	16	0
Aloque a página 10 da extensão uniforme existente	856	108	16	0
Desaloque a página 10 da extensão uniforme existente	856	108	16	0
Desaloque a página 9 e a extensão uniforme	864	100	16	8
Desaloque a página 8	864	100	16	9
Desaloque as páginas de 7 a 1, e desaloque na extensão mista	872	100	16	16

Consulte também

Conceitos

Otimizando o desempenho de tempdb

Banco de dados tempdb

Planejamento de capacidade para tempdb

Outros recursos

Solucionando problemas (Mecanismo de Banco de Dados)

Compartilhar via