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_time

FROM 

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 qt

WHERE 

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前後的使用情況。