SQL Server - Hanging Process - SPID Query

sql-server spid
Chris picture Chris · Mar 4, 2011 · Viewed 54.7k times · Source

How do I find what is the associated query that is being executed if I have the SPID. Trying to figure out what query is being associated since the process seems to be hung.

Answer

user2661347 picture user2661347 · Jan 28, 2014 
--Find Current SQL Statements that are Running
SELECT   SPID           = er.session_id
        ,STATUS         = ses.STATUS
        ,[Login]        = ses.login_name
        ,Host           = ses.host_name
        ,BlkBy          = er.blocking_session_id
        ,DBName         = DB_Name(er.database_id)
        ,CommandType    = er.command
        ,ObjectName     = OBJECT_NAME(st.objectid)
        ,CPUTime        = er.cpu_time
        ,StartTime      = er.start_time
        ,TimeElapsed    = CAST(GETDATE() - er.start_time AS TIME)
        ,SQLStatement   = st.text
FROM    sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses
        ON ses.session_id = er.session_id
    LEFT JOIN sys.dm_exec_connections con
        ON con.session_id = ses.session_id
WHERE   st.text IS NOT NULL

Related questions

Process/SPID is being blocked by itself, how to clear/kill without restarting Sql Server

We have a process that was running for 4 hours. Because it was running so long, it was causing other issues in the database, so it was decided to kill the process. Now, the process is in a suspended state. It …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More