How to view parameters of currently running processes in SQL Server 2008

Question

I am trying to fix a problem that occurs on our new SQL Server. When viewing running processes ( sp_who2 ), I cannot determine with what parameters the process was started.

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

I can even find additional information, but I see no 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 that I can see the parameters if I do a trace, but in this case it does not help.

Answer 1

You need to check out Adam Machanic SP_WhoisActive, it gives you all the necessary information and gives you the whole request in xml so you can just click on it and see what works.
http://sqlblog.com/files/folders/release/entry29675.aspx

Answer 2

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.

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

It seems you cannot do it without a trace. There are some more posts with a similar question: Is it possible to fully parameterize queries using DBCC INPUTBUFFER?

You can use DBCC INPUTBUFFER (spid), but only if sp starts from SSMS.

Take a look at http://sqlblog.com/tags/Who+is+Active/default.aspx . If you cannot add this sp to your square, the code will be useful.

Edit: it will only show which instruction of your sp is running, not the parameters.

Answer 3

Can you modify a saved process to register parameters? Perhaps insert in the table the parameters for diagnosis?