SQL Server performance – Death by xp_cmdshell

In SQL server there is an extended procedure, called xp_cmdshell, which you can use to execute commands and programs outside the SQL server process. It can be useful if you need to do something that is not supported by the T-SQL language, like secure copy, BUT there are some major risks attached to the use of xp_cmdshell. Personally, I am not a frient of xp_cmdshell.

Besides the security risk there is another risk that may strike you like lightning. What happens if you are using xp_cmdshell within a procedure called from SQL agent and the program you execute throws an error that can’t be caught by xp_xmdshell.

Assume you are using xp_cmdshell to execute secure copy and the program can’t find the path specified. Xp_cmdshell will wait for a response from secure copy, but secure copy doesn’t send a response and xp_cmdshell will wait for a long, long, long time. That’s what happened to a client recently.

When you finally realize that you have a process that is hung, the first thing you probably will try is to kill the process. But what will happen? If you kill a process with a transaction, SQL server will roll back the transaction, but how in the world are going to roll back a transaction with a failing external program!?

Now you have a process (spid) that is stuck in the middle of a restore, and the worst part is that any locks acquired on rows, pages or tables are not released.

Assume that no locks are acquired and you can live with the stuck process for a while, until you can restart the SQL service instance. What happens with the SQL Agent job that called the procedure in the first place? Well, you can’t restart the job, because SQL Agent job is still running.

How do you solve this issue? The most obvious, is to avoid the issue in the first place.