[SQL Server] Afficher liste sessions utilisent actuellement TempDB
SELECT
T4.dbid AS QueryExecutionContextDBID,
DB_NAME(T4.dbid) AS QueryExecContextDBNAME,
T4.objectid AS ModuleObjectId,
SUBSTRING(T4.TEXT,
T2.statement_start_offset/2 + 1,
(CASE WHEN T2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),T4.TEXT)) * 2
ELSE T2.statement_end_offset
END - T2.statement_start_offset)/2) AS Query_Text,
T1.session_id ,
T1.request_id,
T1.exec_context_id,
(T1.user_objects_alloc_page_count - T1.user_objects_dealloc_page_count) AS NbrePagesUtilisateursEnAttente,
(T1.internal_objects_alloc_page_count - T1.internal_objects_dealloc_page_count) AS NombrePagesObjetsInternesEnAttente,
T2.start_time,
T2.command,
T2.open_transaction_count,
T2.percent_complete,
T2.estimated_completion_time,
T2.cpu_time,
T2.total_elapsed_time,
T2.reads,T2.writes,
T2.logical_reads,
T2.granted_query_memory,
T3.HOST_NAME,
T3.login_name,
T3.program_name
FROM sys.dm_db_task_space_usage T1
INNER JOIN sys.dm_exec_requests T2
ON (T1.session_id = T2.session_id AND T1.request_id = T2.request_id)
INNER JOIN sys.dm_exec_sessions T3 ON (T1.session_id = T3.session_id)
CROSS APPLY sys.dm_exec_sql_text(T2.sql_handle) T4
WHERE (T1.internal_objects_alloc_page_count + T1.user_objects_alloc_page_count) > 0
ORDER BY (T1.user_objects_alloc_page_count - T1.user_objects_dealloc_page_count) + (T1.internal_objects_alloc_page_count - T1.internal_objects_dealloc_page_count) DESC