SQL Server - Hanging Process - SPID Query

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 sinc开发者_StackOverflow社区e the process seems to be hung.

--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

what version of sql server? for 2000 and up you can do

dbcc inputbuffer (spid)

That will give the first 255 characters

replace spid with the numerical number for spid

for 2005 and up, change @@SPID to the spid you are looking for

select dest.*
from  sys.dm_exec_requests as der
             cross apply sys.dm_exec_sql_text (der.sql_handle) as dest
where session_id = @@spid

Assuming SQL Server 2005+

SELECT
    CASE
        WHEN statement_end_offset = -1
        THEN text
        ELSE SUBSTRING(text,statement_start_offset/2,(statement_end_offset- statement_start_offset)/2)
    END, *
FROM    sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id = <whatever>

You might want to download the "Who is Active?" procedure that pulls together a lot more information than that.

If you look at it in the activity monitor you should be able to find the SQL associated with the SPID by right clicking and selecting "details".

Here are a few links to get you started:

• http://support.microsoft.com/kb/298475
• http://msdn.microsoft.com/en-us/library/ms188272.aspx
• http://msdn.microsoft.com/en-us/library/ms175518.aspx

You can also kill it from there as a last resort.