List the queries running on SQL Server

Is there a way to list the queries that are currently running on MS SQL Server (either through the Enterprise Manager or SQL) and/or who's connected?

I think I've got a very long running query is being execute on one of my database servers and I'd like to track it down and stop it (or the person who keeps starting it).

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


[1]:

[To see links please register here]

[2]:

[To see links please register here]

Use Sql Server Profiler (tools menu) to monitor executing queries and use activity monitor in Management studio to see how is connected and if their connection is blocking other connections.

in 2005 you can right click on a database, go to reports and there's a whole list of reports on transitions and locks etc...

here is a query that will show any queries that are blocking. I am not entirely sure if it will just show slow queries:

SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.sid = l.sid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )

In the Object Explorer, drill-down to: Server -> Management -> Activity Monitor. This will allow you to see all connections on to the current server.

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

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

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


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

[5]:

[To see links please register here]

[6]:

[To see links please register here]

[7]:

[To see links please register here]

The right script would be like this:

select
p.spid, p.status,p.hostname,p.loginame,p.cpu,r.start_time, t.text
from sys.dm_exec_requests as r, sys.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) t
where p.status not in ('sleeping', 'background')
and r.session_id=p.spid

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][1]
- More about [sys.dm_exec_sql_text][2]

[1]:

[To see links please register here]

[2]:

[To see links please register here]

I would suggest querying the `sys` views. something similar to

SELECT *
FROM
sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
LEFT JOIN sys.dm_db_task_space_usage tsu
ON tsu.session_id = s.session_id
LEFT JOIN sys.dm_os_tasks t
ON t.session_id = tsu.session_id
AND t.request_id = tsu.request_id
LEFT JOIN sys.dm_exec_requests r
ON r.session_id = tsu.session_id
AND r.request_id = tsu.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL

This way you can get a `TotalPagesAllocated` which can help you figure out the `spid` that is taking all the server resources. There has lots of times when I can't even bring up activity monitor and use these `sys` views to see what's going on.

I would recommend you reading the following article. [I got this reference from here](

[To see links please register here]

).