Query for Queries

This is a query to view all current activity on the database server (assuming you have the proper permissions). You can see who is connected, if they have any open transactions, if they are blocked or blocking, and what exactly they are executing.

This is similar to the SQL Activity view in Spotlight but can be more useful if you need to apply advanced filtering. You can even filter for specific text inside the user queries, for example if you needed to see everyone using a particular table.

select	sp.spid,
	sp.[status],
	sp.blocked,
	sdb.name as [Database],
	sp.loginame,
	sp.cpu,
	sp.physical_io,
	sp.login_time,
	sp.last_batch,
	sp.open_tran,
	sp.hostname,
	sp.[program_name],
	sp.cmd,
	qt.[text] as QueryText
from	sysprocesses sp
	inner join sys.databases sdb
		on sdb.database_id = sp.dbid
	cross apply sys.dm_exec_sql_text(SP.sql_handle) qt
order by
	sdb.name asc,
	sp.spid asc;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s