SQLServer的tempdb暴增导致磁盘消耗的处理方案

先查长事务： SELECT transaction_id,session_id FROM sys.dm_tran_active_snapshot_database_transactions  ORDER BY elapsed_time_seconds DESC; 再看这些会话运行了多久：SELECT  es.session_id, --database_name ='HKERP', login_name, er.status, wait_type, individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2), parent_query = qt.text, program_name, host_name, nt_domain, start_timeFROM  sys.dm_exec_requests er    INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qtWHERE  es.session_id > 50               AND es.session_Id NOT IN (@@SPID) ORDER BY  1, 2  看看有没阻塞： SELECT  s.loginame        ,[Individual Query] = SUBSTRING (qr.text,qs.statement_start_offset/2,           (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2                 ELSE qs.statement_end_offset            END - qs.statement_start_offset)/2)         ,qs.session_id ,s.counts AS [进程个数],qs.status ,qs.blocking_session_id        ,qs.wait_type ,qs.wait_time ,qs.wait_resource         ,qs.transaction_id  FROM SYS.DM_EXEC_REQUESTS qs (nolock)   LEFT JOIN (              SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES (nolock) GROUP BY spid             ) s ON qs.session_id=s.spid   OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr WHERE qs.status = N'suspended' --and s.loginame<>'' ORDER BY qs.wait_time DESC --查找阻塞源头v3.0 SELECT SP.spid       ,CASE WHEN ST1.text IS NULL THEN ST2.text             ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2,                   (                   CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2                         ELSE SR.statement_end_offset                    END - SR.statement_start_offset)/2                   )         END AS [T-sql]       ,SP.loginame       ,DB_NAME(SP.dbid) AS [db_name]       ,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.* FROM SYS.SYSPROCESSES SP (nolock)    LEFT JOIN SYS.DM_EXEC_REQUESTS SR (nolock) ON SP.spid=SR.session_id   LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC (nolock) ON SP.spid=SC.session_id   OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2   OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1 WHERE SP.spid IN       (          SELECT BLOCKED FROM SYS.SYSPROCESSES (nolock) WHERE BLOCKED<>0 and  lastwaittype<>'MISCELLANEOUS'       )   AND SP.BLOCKED=0 杀了阻塞源，或者是长事务，一般就能解决问题了。 同时对比tempdb前后的使用情况。