Xp_cmdshell freezes after exe exe exits

Question

I have a problem with freezing using xp_cmdshell.

• The executable file is called, executes its work, and terminates. It does not hang due to the ui prompt in exe. Exe is not hanging at all. Exe disappears from the list of processes in the task manager, and internal logging from exe confirms that it has completed the very last line in the main function

• calling xp_cmdshell does NOT return control in SQL. It hangs on this line (this is the last line of the batch). Killing a process is ineffective. This actually requires a sql server reboot to get rid of the process freeze (ugh)

• Hanging occurs only at the first start. Subsequent calls to the procedure with the same parameters work and exit correctly while the first one hangs. After restarting SQL, the first subsequent call hangs again.

• If that matters, I'm trying to get the return value from exe - my sql procedure ends:

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

• The activity monitor reports that the process will depend on the type of wait PREEMPTIVE_OS_PROCESSOPS (as seen by another developer) or PREEMPTIVE_OS_PIPEOPS (which I see during my current testing)

Any ideas?

Answer 1

In fact, we really figured out the problem here. The called application was used to automatically upload some documents to the printer when certain conditions arise.

It turns out that a particular print driver has detected a strange small window in the notification tray when it prints. So it was hanging due to the appearance of the ui window - but our application was correctly terminated because it is not our window, it is a window caused by the print driver.

This driver includes the option to disable this window. Our problem disappeared when this option was set.

Answer 2

Just stumbled upon this situation myself, where I ran an invalid comment via xp_cmdshell.

I managed to kill it without restarting SQL, I did this to determine the process that runs the command and kill it from the task manager.

Suppose your SQL worked up in Windows 2008: In the "Task Manager" section of the "Processes" tab. I have included a column to display the command line for each process (for example: View → Select Columns ..).

If you do not know which command you ran through xp_cmdshell, dbcc inputbuffer ( SPID ) should give you the key.

Answer 3

We had the same problem with SQL Server 2008, also with calls related to xp_cmdshell and BCP. Killing the sql process id did not help, it would just stay in the “KILLED / ROLLBACK” state.

The only way to kill him is to kill the bcp.exe process in the Windows task manager.

In the end, we traced the problem to the wrong SQL in the sproc that xp_cmdshell called. This was a mistake when opening multiple transactions in a loop and not closing them. After the BEGIN / COMMIT problems were fixed, PREEMPTIVE_OS_PROCESSOPS no longer returned.