How do I view the parameters of currently running procs in SQL Server 2008

Question

I am trying to troubleshoot an issue that is popping up on our new SQL Server. While viewing the running processes (sp_who2) I can't tell what parameters a proc was started with.

I can find the name of the proc using: DBCC INPUTBUFFER (spid)

I can even find some additional info, but I can't see a way to show the parameters.

(http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-%E2%80%93-get-last-running-query-based-on-spid/)

I know I can see the parameters if I do a trace, but that doesn't help in this case.

Answer 1

You need to check out Adam Machanic's SP_WhoisActive it gives you all the info you need and it gives you the entire query in in xml so you can just click on it and see what is running.
http://whoisactive.com