This script is very useful for checking the SQL statement being stuck inside a batch and stored procedure.
SELECT
CASE WHEN (SELECT COUNT(*) FROM sys.sysprocesses WHERE spid = r.spid) > 1 THEN 'Multithread' ELSE '' END AS Multithread,
LEFT(t.[text], 255) AS [text255],
SUBSTRING(
t.[text],
(r.stmt_start / 2) + 1,
((
CASE
r.stmt_end
WHEN -1
THEN DATALENGTH(t.[text])
ELSE r.stmt_end
END - r.stmt_start) / 2) + 1) AS stmt,
DB_NAME(t.dbid) AS ObjectDB,
OBJECT_NAME(t.objectid, t.dbid) AS Object,
r.spid, r.ecid, r.blocked, r.waittime, r.lastwaittype, r.waitresource, DB_NAME(r.dbid) AS connectDB, r.cpu, r.physical_io, r.memusage, r.login_time,
r.last_batch, r.open_tran, r.status, r.hostname, r.program_name, r.loginame, r.cmd, r.net_library, r.login_time, r.stmt_start, r.stmt_end
FROM sys.sysprocesses AS r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.status IN ('runnable', 'suspended', 'running', 'rollback', 'pending', 'spinloop')
ORDER BY spid, ecid