[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
WHERE   st.text IS NOT NULL

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+

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

Source: stackoverflow.com

C# - SQLClient - Simplest INSERT

How do I add a Line break in a SQL Server Stored Procedure?

Real vs. Floating Point vs. Money

Stored Procedures to .sql files

Which is faster? Multiple DELETE statements or a single DELETE Statement using "IN ('x', ''y')

Convert SELECT to an update statement

Why is SQL Server losing a millisecond?

Best way to get the next id number without "identity

How can I resolve my collation conflict in a better way?

SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.