De http://www.sqlservercentral.com/scripts/tempdb/72007/

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    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 [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       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 [statement text],
       EQP.query_plan
FROM task_space_usage 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;

EDITAR

Como Martin apontou em um comentário, isso não localizaria transações ativas que estão ocupando espaço no tempdb, ele encontrará apenas consultas ativas que estão atualmente utilizando espaço lá (e provavelmente culpados pelo uso atual do log). Portanto, pode haver uma transação aberta, mas a consulta real que causa o problema não está mais em execução.

Você pode alterar inner joinon sys.dm_exec_requestspara a left outer joine retornará linhas para sessões que não estão executando consultas ativamente no momento.

A consulta que Martin postou...

SELECT database_transaction_log_bytes_reserved,session_id 
  FROM sys.dm_tran_database_transactions AS tdt 
  INNER JOIN sys.dm_tran_session_transactions AS tst 
  ON tdt.transaction_id = tst.transaction_id 
  WHERE database_id = 2;

... identificaria session_ids com transações ativas que estão ocupando espaço de log, mas você não seria necessariamente capaz de determinar a consulta real que causou o problema, pois se não estiver em execução agora, não será capturada na consulta acima para solicitações ativas. Você pode verificar de forma reativa a consulta mais recente usando DBCC INPUTBUFFER, mas pode não dizer o que você deseja ouvir. Você pode fazer a junção externa de maneira semelhante para capturar aqueles em execução ativa, por exemplo:

SELECT tdt.database_transaction_log_bytes_reserved,tst.session_id,
       t.[text], [statement] = COALESCE(NULLIF(
         SUBSTRING(
           t.[text],
           r.statement_start_offset / 2,
           CASE WHEN r.statement_end_offset < r.statement_start_offset
             THEN 0
             ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
         ), ''
       ), t.[text])
     FROM sys.dm_tran_database_transactions AS tdt
     INNER JOIN sys.dm_tran_session_transactions AS tst
     ON tdt.transaction_id = tst.transaction_id
         LEFT OUTER JOIN sys.dm_exec_requests AS r
         ON tst.session_id = r.session_id
         OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
     WHERE tdt.database_id = 2;

Você também pode usar o DMV sys.dm_db_session_space_usagepara ver a utilização geral do espaço por sessão (mas, novamente, você pode não obter resultados válidos para a consulta; se a consulta não estiver ativa, o que você receber de volta pode não ser o culpado real).

;WITH s AS
(
    SELECT 
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count 
          + s.internal_objects_alloc_page_count) 
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count 
      + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text], 
  [statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text], 
        r.statement_start_offset / 2, 
        CASE WHEN r.statement_end_offset < r.statement_start_offset 
        THEN 0 
        ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
      ), ''
    ), t.[text])
FROM s
LEFT OUTER JOIN 
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

Com todas essas consultas à sua disposição, você deve ser capaz de restringir quem está usando tempdb e como, especialmente se você pegá-los em flagrante.

algumas dicas para minimizar a utilização do tempdb

1. use menos tabelas #temp e variáveis ​​@table
2. minimizar a manutenção simultânea do índice e evitar a SORT_IN_TEMPDBopção se não for necessária
3. evite cursores desnecessários; evite cursores estáticos se você achar que isso pode ser um gargalo, pois os cursores estáticos usam tabelas de trabalho em tempdb
4. tente evitar spools (por exemplo, grandes CTEs que são referenciados várias vezes na consulta)
5. não use MARTE
6. teste minuciosamente o uso de níveis de isolamento de instantâneo / RCSI - não apenas ative-o para todos os bancos de dados, pois foi informado que é melhor que NOLOCK (é, mas não é gratuito)
7. em alguns casos, pode parecer pouco intuitivo, mas use mais tabelas temporárias. por exemplo, dividir uma consulta enorme em partes pode ser um pouco menos eficiente, mas se puder evitar um enorme vazamento de memória para o tempdb porque a consulta única e maior requer uma concessão de memória muito grande ...
8. evite ativar acionadores para operações em massa
9. evite o uso excessivo de tipos LOB (tipos máximos, XML, etc) como variáveis ​​locais
10. mantenha as transações curtas e agradáveis
11. não defina tempdb para ser o banco de dados padrão de todos -

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

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/17d9f862-b9ae-42de-ada0-4229f56712dc/tempdb-log-filling-cannot-find-how-or-what?forum=sqldatabaseengine

 SELECT tst.[session_id],
            s.[login_name] AS [Login Name],
            DB_NAME (tdt.database_id) AS [Database],
            tdt.[database_transaction_begin_time] AS [Begin Time],
            tdt.[database_transaction_log_record_count] AS [Log Records],
            tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
            tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
            SUBSTRING(st.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
            END - r.statement_start_offset)/2) + 1) AS statement_text,
            st.[text] AS [Last T-SQL Text],
            qp.[query_plan] AS [Last Plan]
    FROM    sys.dm_tran_database_transactions tdt
            JOIN sys.dm_tran_session_transactions tst
                ON tst.[transaction_id] = tdt.[transaction_id]
            JOIN sys.[dm_exec_sessions] s
                ON s.[session_id] = tst.[session_id]
            JOIN sys.dm_exec_connections c
                ON c.[session_id] = tst.[session_id]
            LEFT OUTER JOIN sys.dm_exec_requests r
                ON r.[session_id] = tst.[session_id]
            CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
            OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
    WHERE   DB_NAME (tdt.database_id) = 'tempdb'
    ORDER BY [Log Bytes Used] DESC
GO

Obrigado por este post, provavelmente o único de seu tipo. Meu teste foi simples, crie uma tabela temporária e certifique-se de que ela apareça quando eu executar qualquer uma das consultas deste post... apenas uma ou duas foram realmente bem-sucedidas. Corrigi-o para se juntar ao T-SQL, otimizei-o para execuções mais longas e o tornei bastante útil. Deixe-me saber se eu perdi alguma coisa, mas até agora você tem um script automatizado / em loop. Ele fornece uma maneira de avaliar qual consulta/SPID é o infrator durante um período de tempo usando a consulta de desvio padrão (STDEV) abaixo.

Isso é executado a cada 3 minutos por 40 vezes, ou seja, 2 horas. Modifique os parâmetros como achar melhor.

Há um filtro WHERE > 50 páginas abaixo que as pessoas podem querer limpar caso você tenha muitas tabelas pequenas. Caso contrário, você não vai pegar essa nuance com o abaixo como é ...

Apreciar!

DECLARE @minutes_apart INT; SET @minutes_apart = 3
DECLARE @how_many_times INT; SET @how_many_times = 40
--DROP TABLE tempdb..TempDBUsage
--SELECT * FROM tempdb..TempDBUsage
--SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC

DECLARE @delay_string NVARCHAR(8); SET @delay_string = '00:' + RIGHT('0'+ISNULL(CAST(@minutes_apart AS NVARCHAR(2)), ''),2) + ':00'
DECLARE @counter INT; SET @counter = 1

SET NOCOUNT ON
if object_id('tempdb..TempDBUsage') is null
    begin
    CREATE TABLE tempdb..TempDBUsage (
        session_id INT, pages INT, num_reads INT, num_writes INT, login_time DATETIME, last_batch DATETIME,
        cpu INT, physical_io INT, hostname NVARCHAR(64), program_name NVARCHAR(128), text NVARCHAR (MAX)
    )
    end
else
    begin
        PRINT 'To view the results run this:'
        PRINT 'SELECT * FROM tempdb..TempDBUsage'
        PRINT 'OR'
        PRINT 'SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC'
        PRINT ''
        PRINT ''
        PRINT 'Otherwise manually drop the table by running the following, then re-run the script:'
        PRINT 'DROP TABLE tempdb..TempDBUsage'
        RETURN
    end
--GO
TRUNCATE TABLE tempdb..TempDBUsage
PRINT 'To view the results run this:'; PRINT 'SELECT * FROM tempdb..TempDBUsage'
PRINT 'OR'; PRINT 'SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC'
PRINT ''; PRINT ''

while @counter <= @how_many_times
begin
INSERT INTO tempdb..TempDBUsage (session_id,pages,num_reads,num_writes,login_time,last_batch,cpu,physical_io,hostname,program_name,text)
    SELECT PAGES.session_id, PAGES.pages, r.num_reads, r.num_writes, sp.login_time, sp.last_batch, sp.cpu, sp.physical_io, sp.hostname, sp.program_name, t.text
    FROM sys.dm_exec_connections AS r
    LEFT OUTER JOIN master.sys.sysprocesses AS sp on sp.spid=r.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.most_recent_sql_handle) AS t
    LEFT OUTER JOIN (
        SELECT s.session_id, [pages] = SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count) 
        FROM sys.dm_db_session_space_usage AS s
        GROUP BY s.session_id
        HAVING SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count) > 0
    ) PAGES ON PAGES.session_id = r.session_id
    WHERE PAGES.session_id IS NOT NULL AND PAGES.pages > 50
    ORDER BY PAGES.pages DESC;
PRINT CONVERT(char(10), @counter) + ': Ran at: ' + CONVERT(char(30), GETDATE())
SET @counter = @counter + 1
waitfor delay @delay_string
end

Infelizmente, o log do tempDB não pode ser rastreado diretamente para os sessionIDs visualizando os processos em execução.

Reduza o arquivo de log tempDB até um ponto em que ele cresça significativamente novamente. Em seguida, crie um evento estendido para capturar o crescimento do log. Quando ele crescer novamente, você poderá expandir o evento estendido e visualizar o arquivo de evento do pacote. Abra o arquivo, adicione um filtro de tempo, filtro de tipo de arquivo (você não deseja que os resultados do arquivo de dados sejam incluídos) e, em seguida, agrupe-o por ID de sessão no SSMS. Isso irá ajudá-lo a encontrar o(s) culpado(s), pois você está procurando por ids de sessão com o maior número de grupos. Claro que você precisa coletar o que está sendo executado nos IDs de sessão por meio de outro processo ou ferramenta. Talvez alguém saiba como obter a consulta da coluna query_hash e tenha a gentileza de postar a solução.

Resultados do evento estendido:

Script para criar o evento estendido:

CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER ADD EVENT 
sqlserver.database_file_size_change(SET collect_database_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)