Monitoring SQL processes

Scripts to displays job information for all jobs that currently have a status of "Executing"

exec msdb.dbo.sp_help_job @execution_status=1

Scripts to displays job information for all "running" jobs

exec msdb.dbo.sp_help_job @execution_status=0

Scripts to display suspended processes

SELECT req.session_id
	,req.start_time
	,req.wait_type
	,substring(ses.host_name, 1, 20) AS host_name
	,substring(ses.login_name, 1, 30) AS login_name
	,req.command
	,ses.host_name
	,ses.program_name
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions ses ON req.session_id = ses.session_id
WHERE req.STATUS = 'SUSPENDED'
ORDER BY req.start_time

Scripts to see the last batch that was run by a connection

SELECT session, text
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE session_id = @session