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/

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐