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/
上一篇: 白菜怎么做好吃,让你感受不同的白菜味道
下一篇: MySQL逻辑备份mysqldump