Solucionando problemas de espaço insuficiente em disco 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;

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;

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.

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;

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;

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

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

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;

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;

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.