DMV Query For Current Executing SQL Statement

Often times for troublshooting a running big batch of query (for example a migration script) you would need to know the current sql statement executing. You can use below query after identifying the session id (spid) for the batch :

SELECT SUBSTRING(t.[text],
1+(r.statement_start_offset/2),
1+(
(
CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset
)/2
)
)
FROM
sys.dm_exec_sessions s LEFT JOIN
sys.dm_exec_requests r ON (r.session_id = s.session_id) CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = ? /* Session id that you need to investigate */