If you don’t have DBArtisan or other more advanced SQL Editor, you can use the query below. Take note that you’ll need necessary rights to be able to run it.
select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
hostname, hostprocess, loginame, program_name, net_address,
net_library, dbid, ecid, kpid, nt_domain, nt_username, uid, sid,
memusage, last_batch=convert(varchar(26), last_batch,121),
--where (blocked!=0 or waittype != 0x0000)
If you only want to see those that are blocking or locking, uncomment the last line. One thing that’s lacking here is the list of tables and database objects that each process id is using. That’s important if a user is running stored procedures and you need to know . I’ll provide that later as honestly, I don’t know yet which table holds that information.
I actually contributed this at SQL Reviewer as I don’t see a lot of websites that discusses this. For further reading, you may go to the MSDN website.