How to monitor tempdb in MS SQL

Error: tempdb is full due to active_transaction.

 

 1 select 
 2 ss.[host_name],
 3 ss.login_name,
 4 ss.original_login_name,
 5 ss.[status],
 6 R.*
 7 from(
 8     SELECT  COALESCE(T1.session_id, T2.session_id) [session_id] , 
 9             T1.request_id ,
10             COALESCE(T1.database_id, T2.database_id) [database_id],
11             DB_NAME(COALESCE(T1.database_id, T2.database_id)) as Database_Name,        
12             COALESCE(T1.[Total Allocation User Objects], 0) + T2.[Total Allocation User Objects] [Total Allocation User Objects (MB)] ,
13             COALESCE(T1.[Net Allocation User Objects], 0) + T2.[Net Allocation User Objects] [Net Allocation User Objects] ,
14             COALESCE(T1.[Total Allocation Internal Objects], 0) + T2.[Total Allocation Internal Objects] [Total Allocation Internal Objects (MB)] ,
15             COALESCE(T1.[Net Allocation Internal Objects], 0) + T2.[Net Allocation Internal Objects] [Net Allocation Internal Objects (MB)] ,
16             COALESCE(T1.[Total Allocation], 0) + T2.[Total Allocation] [Total Allocation (MB)] ,
17             COALESCE(T1.[Net Allocation], 0) + T2.[Net Allocation] [Net Allocation (MB)] ,
18             COALESCE(T1.[Query Text], T2.[Query Text]) [Query Text]
19     FROM    (( SELECT   TS.session_id,
20               TS.request_id,
21               TS.database_id,
22               CAST(TS.user_objects_alloc_page_count / 128. AS DECIMAL(15,2)) [Total Allocation User Objects] ,
23               CAST((TS.user_objects_alloc_page_count - TS.user_objects_dealloc_page_count) / 128. AS DECIMAL(15,2)) [Net Allocation User Objects] ,
24               CAST(TS.internal_objects_alloc_page_count / 128. AS DECIMAL(15,2)) [Total Allocation Internal Objects] ,
25               CAST((TS.internal_objects_alloc_page_count - TS.internal_objects_dealloc_page_count) / 128. AS DECIMAL(15,2)) [Net Allocation Internal Objects] ,
26               CAST((TS.user_objects_alloc_page_count + internal_objects_alloc_page_count) / 128. AS DECIMAL(15,2)) [Total Allocation] ,
27               CAST((TS.user_objects_alloc_page_count + TS.internal_objects_alloc_page_count - TS.internal_objects_dealloc_page_count - TS.user_objects_dealloc_page_count) / 128. AS DECIMAL(15,2)) [Net Allocation] ,
28               T.text [Query Text]
29               FROM  sys.dm_db_task_space_usage TS
30                     INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id AND ER.session_id = TS.session_id
31                     OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
32             ) T1
33             RIGHT JOIN 
34             ( SELECT  SS.session_id,
35                           SS.database_id,
36                           CAST(SS.user_objects_alloc_page_count / 128. AS DECIMAL(15,2)) [Total Allocation User Objects] ,
37                           CAST((SS.user_objects_alloc_page_count - SS.user_objects_dealloc_page_count)/ 128. AS DECIMAL(15, 2)) [Net Allocation User Objects] ,
38                           CAST(SS.internal_objects_alloc_page_count / 128. AS DECIMAL(15,2)) [Total Allocation Internal Objects] ,
39                           CAST((SS.internal_objects_alloc_page_count - SS.internal_objects_dealloc_page_count) / 128. AS DECIMAL(15, 2)) [Net Allocation Internal Objects] ,
40                           CAST((SS.user_objects_alloc_page_count + internal_objects_alloc_page_count) / 128. AS DECIMAL(15, 2)) [Total Allocation] ,
41                           CAST((SS.user_objects_alloc_page_count + SS.internal_objects_alloc_page_count - SS.internal_objects_dealloc_page_count - SS.user_objects_dealloc_page_count) / 128. AS DECIMAL(15, 2)) [Net Allocation] ,
42                           T.text [Query Text]
43                FROM   sys.dm_db_session_space_usage SS
44                       LEFT JOIN sys.dm_exec_connections CN ON CN.session_id = SS.session_id
45                       OUTER APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) T
46              ) T2 ON T1.session_id = T2.session_id)
47 ) R
48 left join sys.dm_exec_sessions ss on R.session_id=ss.session_id
49 order by ss.status, [Total Allocation User Objects (MB)] desc

 

 

参考链接：https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/

转载于:https://www.cnblogs.com/HopperYang/p/9802754.html