欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

How to monitor tempdb in MS SQL

程序员文章站 2022-05-18 14:26:26
Error: tempdb is full due to active_transaction. 参考链接:https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/ ......

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/