SQL Server 2008 Running Procs: How to get the xp_cmdshell items?

Question

I'm using this query to find some queries that have been running for a long time:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
order by req.total_elapsed_time des 

Currently, we have some xp_cmdshell things that seem to be stuck (we use it to call bcp.exe for bulk exporting in jobs). However, the output of sys.dm_exec_sql_text() only outputs "xp_cmdshell" and not the parameters -- I really would like to see exactly what commands xp_cmdshell is running so I can track down the issues.

Is there any way to do that in SQL server?

EDIT: The active sessions are calling xp_cmdshell from a stored procedure. E.g.:

EXEC usp_xxx -> calls EXEC usp_yyy -> calls xp_cmdshell.

So, the output of DBCC InputBuffer is the call to usp_xxx which is not what I want.

Answer 1

I'm not sure if the details are available in any of the dynamic management views, but you could take your session_id and use it with DBCC INPUTBUFFER to get the details you seek.

DBCC INPUTBUFFER(83)

would return something like this as an example

EventType           Parameters      EventInfo
--------------      ----------      ------------------------
Language Event      0               EXEC xp_cmdshell 'sc /h'