See what queries are currently running

Sometimes you required to see what is currently running on a SQL server. Its usually required when any query is running from very long time and you need to tran what query is running On SQL Server 200

Sometimes you required to see what is currently running on a SQL server. Its usually required when any query is running from very long time and you need to tran what query is running
On SQL Server 2005 and later this is easy to achieve using View dm_exec_requests (Dynamic Management).

For this you required proper rights on database.


SELECT      
	r.start_time StartTime,
	DATEDIFF(SECOND,r.start_time ,GETDATE()) Duration ,
	r.session_ID,
	DB_NAME(database_id) [Database],
	SUBSTRING(t.text,(r.statement_start_offset/2)+1,
	CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
	THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
	ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
	END) [Executing SQL],
	Status,command,wait_type,wait_time,wait_resource,
	last_wait_type
FROM        
	sys.dm_exec_requests r
	OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       
	--session_id != @@SPID AND  -- don't show this query
	session_id > 50 -- don't show system queries
ORDER BY    
	r.start_time
Login for comment