This query will show a list of the SQL Processes. It is the list that will appear in the Activity Monitor's Processes section.

If you need to filter the list by a specific database, you can remove the comment from the where clause.

Note: As of Version 04.60.1203.2901, this is built into Control and can be accessed by going to the Tools Menu, under Utilities, and clicking Export SQL Processes. Or typing Ctrl-Shift-Q.

None. This is a selection query and no data is modified in the running of it.

USE Master;
SELECT P.blocked Is_Blocked, P.spid Process_ID, D.name Database_Name, P.dbid Database_ID, 
       P.program_name Program_Name, P.loginame Login_Name, P.hostname Host_Name,
       P.cmd Command, S.text QueryText, P.login_time Login_Time, P.last_batch Last_Batch_Time, 
       P.status STATUS, P.kpid Windows_Thread_ID, P.waittype Wait_Type, P.waittime Wait_Time, 
       P.lastwaittype Last_Wait_Type, P.waitresource Wait_Resource, P.uid User_ID, 
       P.cpu Cumulative_CPU_Time , P.physical_io Cumulative_Disk_IO, P.memusage Pages_In_Memory,
       P.ecid Execution_ID, P.open_tran Transactions_Open, P.sid User_GUID, 
       P.hostprocess Workstation_ID, P.net_address Network_ID, P.net_library Network_Type, 
       P.sql_handle Execution_Batch, P.request_id Request_ID
 
FROM sys.sysprocesses P
     LEFT JOIN sys.databases D ON (D.database_id = P.dbid)
     CROSS APPLY sys.dm_exec_sql_text(sql_handle) S
 
--where D.Name = 'StoreData'
ORDER BY P.blocked DESC, D.name, P.program_name, P.hostname, P.loginame
  • Entered : 10/21/2011
  • Version :
You could leave a comment if you were logged in.