Long Running Queries Along with Kill Statement of SPID

drop table #a

go

SELECT

      HOST_NAME               AS [System Name],

      program_name                  AS [Application Name],

      DB_NAME(database_id)    AS [DATABASE Name],

      USER_NAME(USER_ID)      AS [USER Name],

      connection_id                 AS [CONNECTION ID],

      sys.dm_exec_requests.session_id AS [CURRENT SESSION ID],

      blocking_session_id           AS [Blocking SESSION ID],

      start_time              AS [Request START TIME],

      sys.dm_exec_requests.status   AS [Status],

      command AS [Command Type],

      (SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS [Query TEXT],

      wait_type               AS [Waiting Type],

      wait_time               AS [Waiting Duration],

      wait_resource                 AS [Waiting FOR Resource],

      sys.dm_exec_requests.transaction_id AS [TRANSACTION ID],

      percent_complete        AS [PERCENT Completed],

      estimated_completion_time     AS [Estimated COMPLETION TIME (in mili sec)],

      sys.dm_exec_requests.cpu_time       AS [CPU TIME used (in mili sec)],

      (memory_usage * 8)      AS [Memory USAGE (in KB)],

      sys.dm_exec_requests.total_elapsed_time AS [Elapsed TIME (in mili sec)] into #a

FROM sys.dm_exec_requests

            INNER join

      sys.dm_exec_sessions

            ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id

WHERE DB_NAME(database_id) = 'DBNAME'

GO

select 'kill ' + cast([CURRENT SESSION ID] as varchar) as Statement, * from #a where [Query TEXT] like '%procedurename%'

go