List the queries running on SQL Server

Solution 1:

This will show you the longest running SPIDs on a SQL 2000 or SQL 2005 server:

select
    P.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

If you need to see the SQL running for a given spid from the results, use something like this:

declare
    @spid int
,   @stmt_start int
,   @stmt_end int
,   @sql_handle binary(20)

set @spid = XXX -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from    sys.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING(  text,
            COALESCE(NULLIF(@stmt_start, 0), 1),
            CASE @stmt_end
                WHEN -1
                    THEN DATALENGTH(text)
                ELSE
                    (@stmt_end - @stmt_start)
                END
        )
FROM ::fn_get_sql(@sql_handle)

Solution 2:

If you're running SQL Server 2005 or 2008, you could use the DMV's to find this...

SELECT  *
FROM    sys.dm_exec_requests  
        CROSS APPLY sys.dm_exec_sql_text(sql_handle)  

• More about sys.dm_exec_requests
• More about sys.dm_exec_sql_text

Solution 3:

You can run the sp_who command to get a list of all the current users, sessions and processes. You can then run the KILL command on any spid that is blocking others.

Solution 4:

There are various management views built into the product. On SQL 2000 you'd use sysprocesses. On SQL 2K5 there are more views like sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests.

There are also procedures like sp_who that leverage these views. In 2K5 Management Studio you also get Activity Monitor.

And last but not least there are community contributed scripts like the Who Is Active by Adam Machanic.