[ACCEPTED]-SQL Server - Hanging Process - SPID Query-spid

Accepted answer
Score: 18
--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


Score: 12

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

dbcc inputbuffer (spid)

That will give the first 255 3 characters

replace spid with the numerical 2 number for spid

for 2005 and up, change @@SPID 1 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
Score: 3

Assuming SQL Server 2005+

        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 3 to download the "Who is Active?" procedure that 2 pulls together a lot more information than 1 that.

Score: 1

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

Here are a few links 2 to get you started:

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

More Related questions