List the queries running on SQL Server

Question

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

User · Answer

Actually  running EXEC sp who2 in Query Analyzer   Management Studio gives more info than sp who   Beyond that you could set up SQL Profiler to watch all of the in and out traffic to the server  Profiler also let you narrow down exactly what you are watching for   For SQL Server 2008   START - All Programs - Microsoft SQL Server 2008 - Performance Tools - SQL Server Profiler   Keep in mind that the profiler is truly a logging and watching app  It will continue to log and watch as long as it is running   It could fill up text files or databases or hard drives  so be careful what you have it watch and for how long

User · Answer

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

User · Answer

You should try very usefull procedure sp whoIsActive which can be found here  http   whoisactive com and it is free

User · Answer

SELECT     p spid  p status  p hostname  p loginame  p cpu  r start time  r command      p program name  text  FROM     sys dm exec requests AS r      master dbo sysprocesses AS p      CROSS APPLY sys dm exec sql text p sql handle  WHERE     p status NOT IN   sleeping    background    AND r session id   p spid

User · Answer

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

User · Answer

Try with this   It will provide you all user queries  Till spid 50 it s all are sql server internal process sessions  But  if you want you can remove where clause   select r session id  r start time  s login name  c client net address  s host name  s program name  st text from sys dm exec requests r inner join sys dm exec sessions s on r session id   s session id left join sys dm exec connections c on r session id   c session id outer apply sys dm exec sql text r sql handle  st where r session id   gt  50

User · Answer

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

User · Answer

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

User · Answer

As a note  the SQL Server Activity Monitor for SQL Server 2008 can be found by right clicking your current server and going to  Activity Monitor  in the context menu  I found this was easiest way to kill processes if you are using the SQL Server Management Studio

User · Answer

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

User · Answer

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

User · Answer

You can use below query to find running last request    SELECT     der session id      est TEXT AS QueryText      der status      der blocking session id      der cpu time      der total elapsed time FROM sys dm exec requests AS der CROSS APPLY sys dm exec sql text sql handle  AS est   Using below script you can also find number of connection per database   SELECT      DB NAME DBID  AS DataBaseName      COUNT DBID  AS NumberOfConnections      LogiName  FROM sys sysprocesses WHERE DBID  gt  0 GROUP BY DBID  LogiName   For more details please visit  http   www dbrnd com 2015 06 script-to-find-running-process-session-logged-user-in-sql-server

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT      p spid  p status  p hostname  p loginame  p cpu  r start time  t text FROM     sys dm exec requests as r      master dbo sysprocesses as 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   And  KILL  spid

User · Answer

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

[sql-server] List the queries running on SQL Server

Examples related to sql-server