6

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?

Clyde
  • 8,017
  • 11
  • 56
  • 87
  • What happens when you don't bother with the `@i`, e.g. just `exec xp_cmdshell @cmd;`? – Aaron Bertrand Mar 08 '12 at 15:50
  • Have not tried that. I need to wait for a window in which we can restart SQL Server. Any idea on how to kill this single process so we don't have to take such a drastic step? – Clyde Mar 08 '12 at 15:58

3 Answers3

5

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.

Dan
  • 51
  • 1
  • 2
3

We had the same issue, with SQL Server 2008, also with calls involving xp_cmdshell and BCP. Killing the sql process ID didn't help, it would just stay stuck in "KILLED/ROLLBACK" status.

Only way to kill it was to kill the bcp.exe process in Windows task manager.

In the end we traced the issue down to wrong SQL in sproc that was calling the xp_cmdshell. It was by mistake opening multiple transactions in a loop and not closing them. After BEGIN/COMMIT trans issues were fixed, PREEMPTIVE_OS_PROCESSOPS never came back again.

userfuser
  • 1,350
  • 1
  • 18
  • 32
0

We actually did eventually figure out the problem here. The app being called was used to automatically dump some documents to a printer when certain conditions happened.

It turns out that a particular print driver popped up a weird little window in the notification tray on a print job. So it was hanging because of a ui window popping up -- but our app was exiting properly because it wasn't our window, it was a window triggered by the print driver.

That driver included an option to turn off that display window. Our problem went away when that option was set.

Clyde
  • 8,017
  • 11
  • 56
  • 87