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

I am trying to troubleshoot an issue that is popping up on our new SQL Server. While viewing the running 开发者_Python百科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.

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

Edit: I found this interesting code: select dest.* from sys.dm_exec_requests as der cross apply sys.dm_exec_sql_text (der.sql_handle) as dest where session_id = @spid. Please try it.

http://msdn.microsoft.com/en-us/library/ms181929%28v=SQL.100%29.aspx

It looks like you can't do it without trace. There few more posts with similar question: Can parameterized queries be fully captured using DBCC INPUTBUFFER?

You can use DBCC INPUTBUFFER (spid) but only if sp is started from SSMS.

Look at this http://sqlblog.com/tags/Who+is+Active/default.aspx. If you can't add this sp to your box, there will be code that can be helpful.

Edit: It will only show what statement of your sp is running, and not parameters.

Can you change the stored proc to log the parameters? Perhaps insert into a table the parameters for diagnostics?