T-SQL to get running sessions and its progress details | SansSQL

Thursday, August 28, 2014

T-SQL to get running sessions and its progress details

Here is a handy T-SQL to get running sessions and its progress details.

SELECT 
  ExecSessions.session_id AS SessionID
 ,ExecRequests.blocking_session_id AS BlockingSessionID
 ,db_name(ExecRequests.database_id) AS DatabaseName
 ,ExecSessions.login_name AS LoginName
 ,ExecSessions.memory_usage AS MemoryUsage
 ,CONVERT(dec(6,3), (ExecRequests.wait_time/60000.00)) AS [WaitTime (mins)]
 ,ExecRequests.[status] AS [Status]
 ,ExecRequests.percent_complete AS PercentComplete
 ,[Text]
FROM sys.dm_exec_sessions AS ExecSessions INNER JOIN sys.dm_exec_requests AS ExecRequests 
ON ExecSessions.session_id = ExecRequests.session_id
CROSS APPLY sys.dm_exec_sql_text (sql_handle) 
WHERE ExecSessions.status = 'running' AND ExecSessions.session_id <> @@SPID
ORDER BY ExecSessions.session_id

No comments:

Post a Comment