xp_cmdshell hangs after called exe has exited

sql-server-2008 xp-cmdshell
Clyde picture Clyde · Mar 8, 2012 · Viewed 9.9k times · Source

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

Dan picture Dan · Aug 28, 2013

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.

Related questions

Access denied for enabled xp_cmdshell for the admin user

I'm using xp_cmdshell within a database trigger to launch a exe file. xp_cmdshell is enabled(it can execute simple cmd command like 'echo'). But when I try to launch the exe through xp_cmdshell, the access is denied. …

Read More
how can i access a file/folder over network through XP_CMDSHELL in sql server 2008?

I am trying to access a folder/directory using 'EXEC MASTER..XP_CMDSHELL' it works for the local file/folder, how ever it can not access the folder over network. EXEC MASTER..XP_CMDSHELL 'c:\Images' --Works fine EXEC MASTER..…

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More