Identificar qual query está enchendo o log do tempdb

Aprenda como Identificar qual query está enchendo o log do tempdb.

Para quem gostaria de identificar qual query está enchendo o log do tempdb abaixo colocarei algumas dicas:

Query:

;WITH tab AS (
 SELECT session_id,
 request_id,
 SUM(internal_objects_alloc_page_count) AS alloc_pages,
 SUM(internal_objects_dealloc_page_count) AS dealloc_pages
 FROM sys.dm_db_task_space_usage WITH (NOLOCK)
 WHERE session_id <> @@SPID
 GROUP BY session_id, request_id
)
SELECT TSU.session_id,
 TSU.alloc_pages * 1.0 / 128 AS [Espaço interno do objeto em MB],
 TSU.dealloc_pages * 1.0 / 128 AS [Espaço Interno do Objeto Desalocado em MB],
 EST.text,
 -- Extraindo o texto da Query
 ISNULL(
 NULLIF(
 SUBSTRING(
 EST.text,
 ERQ.statement_start_offset / 2, 
 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 
 THEN 0 
 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
 ), ''
 ), EST.text
 ) AS [Texto da instrução],
 EQP.query_plan
FROM tab AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
 ON TSU.session_id = ERQ.session_id
 AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

Aqui também vou colocar algumas dicas para minimizar esse crescimento.

1. Use poucas tabelas temporárias #tabela e tabelas variáveis @tabela
2. Minimize a manutenção de índices e evite ordenar índice no tempdb (SORT_IN_TEMPDB) se não for necessário.
3. Evite utilizar cursores, cursores estáticos usam work tables no tempdb.
4. Tente evitar spools
5. Evite triggers em operações de bulk
6. Não defina tempdb para ser o banco de dados padrão de todos.
7. Não use MARS.

Você também pode considerar que seu uso de log tempdb pode ser causado por processos internos que você tem pouco ou nenhum controle sobre eles – por exemplo, database mail, notificações de eventos, notificações de consulta e corretor de serviço todos usam tempdb de alguma forma. Você pode parar de usar esses recursos, mas se você estiver usando-os, você não pode ditar como e quando eles usam tempdb.