xp_cmdshell hangs after called exe has exited

Question

I have a problem with a hang using xp_cmdshell.

1. The executable is called, performs its work, and exits. It is not hanging because of a ui prompt in the exe. The exe is not hanging at all. The exe disappears from the process list in task manager, and internal logging from the exe confirms that it executed the very last line in the main function

2. the call to xp_cmdshell does NOT return control in SQL. It hangs on that line (it is the last line of the batch). Killing the process is ineffective. It actually requires a restart of sql server to get rid of the hung process (ugh)

3. The hang only happens the first time it is run. Subsequent calls to the procedure with identical parameters work and exit correctly so long as the first one is hung. Once SQL is restarted, the first subsequent call will hang again.

4. If it makes any difference, I am trying to receive the return value from the exe -- my sql procedure ends with:

   exec @i = xp_cmdshell @cmd; return @i;

5. Activity Monitor is reporting the process to be stuck on a wait type of PREEMPTIVE_OS_PROCESSOPS (what the other developer saw) or PREEMPTIVE_OS_PIPEOPS (what I'm seeing on my current testing)

Any ideas?

Answer 1

Just came across this situation myself where I've run an invalid comment via xp_cmdshell.

I managed to kill it without restarting SQL, what I've done was to identify the process that run the command and kill it from Task Manager.

Assume your SQL was running in Windows 2008 upward: Under Task Manager, Processes tab. I enabled the column to show Command Line of each process (e.g.: View -> Select Columns..).

If you unsure what command you've run via xp_cmdshell, dbcc inputbuffer(SPID) should give you a clue.