xp_cmdshell works and then doesn't work

Question

xp_cmdshell works and then doesn't work

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

Ed Wagner (5/13/2014)
JustOffal - I'm incredibly curious about this one, too. Please post the command and the output.
Jeff - You know I'm thinking of your presentation that isn't ready yet. 😉

That's what I'm thinking, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JustOffal SSC Eights! Points: 911 More actions · Answer 2

Hi, I'm sorry but I cannot reproduce the error anymore. I removed a few comments from the sp that calls bcp, and it started working again. The issue no longer exists and I don't want it to come back.

Thanks for your responses!

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

Glad to hear it worked out for you in the end.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

David Hurley SSC Rookie Points: 40 More actions · Answer 4

In case this happens again... I recently had the error relating to a NULL result for something that should just simply work - e.g. "dir C:\".At the time this happened, I logged onto the server and was able to see a large number of CMD.exe processes. There were 86 in total! Each one was running under the SQL Server SAC and in theory could be terminated as they look to have been orphaned. I initially tried to terminate them via taskmanager (logged on locally to the server), however "End Process Tree" caused the server to hang for a considerable amount of time - approx. 30 seconds!I used the windows utility - TASKKILL and was able to terminate all CMD.exe calls and without any service detriment (no server / service restart)...taskkill /S serverNAMEorIP /FI "IMAGENAME eq CMD.EXE"TASKKILL - Further information[/url]Happy Days! HTH 🙂

Tried to "unhide" this post because it's not spam. It didn't work, though.

abair34 Ten Centuries Points: 1076 More actions · Answer 5

I'm not sure if this will help but the issue you're describing reminds me of what happens when you exhaust the non interactive desktop heap in windows. Once that happens any attempts to launch more cmd.exe's or bcp.exe's will fail. I'm guessing when this occurs you'll be able to go to the task manager and if you look for processes started by your SQL Server's service account you will not see the typical conhost.exe running. The only other time I remember seeing something similar is when you launched a process from xp_cmdshell and that process never terminated. So in that case you'll see conhost.exe, cmd.exe and some process you told it to launch (like redgate's command line executable). Sometimes then you can kill that launched executable and cmd will complete and then you'll restore connectivity to your xp_cmdshell session. As for the non interactive desktop heap you typically have to make some registry changes to fix it. Another symptom would be if you launched a new process you'd probably find high numerical values for your PIDs (because thousands of other processes were launched before it). Beware that the same high value could just be due to your server being up for a long period of time.

Here's a doc on how to bump up the heap size on windows servers to allow you to launch more commands. You could give this a shot to see if it alleviates the issue.
https://support.microsoft.com/en-us/help/947246/you-may-receive-an-out-of-memory-error-message-because-of-the-desktop-heap-limitation

Hope this helps
-Mike

Bruce.Pantland Grasshopper Points: 13 More actions · Answer 6

abair34 - Friday, February 3, 2017 11:55 AM
I'm not sure if this will help but the issue you're describing reminds me of what happens when you exhaust the non interactive desktop heap in windows. Once that happens any attempts to launch more cmd.exe's or bcp.exe's will fail. I'm guessing when this occurs you'll be able to go to the task manager and if you look for processes started by your SQL Server's service account you will not see the typical conhost.exe running. The only other time I remember seeing something similar is when you launched a process from xp_cmdshell and that process never terminated. So in that case you'll see conhost.exe, cmd.exe and some process you told it to launch (like redgate's command line executable). Sometimes then you can kill that launched executable and cmd will complete and then you'll restore connectivity to your xp_cmdshell session. As for the non interactive desktop heap you typically have to make some registry changes to fix it. Another symptom would be if you launched a new process you'd probably find high numerical values for your PIDs (because thousands of other processes were launched before it). Beware that the same high value could just be due to your server being up for a long period of time.
Here's a doc on how to bump up the heap size on windows servers to allow you to launch more commands. You could give this a shot to see if it alleviates the issue.
https://support.microsoft.com/en-us/help/947246/you-may-receive-an-out-of-memory-error-message-because-of-the-desktop-heap-limitation
Hope this helps
-Mike

Thank you Mike. This was most helpful. I have a legacy system which I need to maintain and one of the stored procedures uses xp_cmdshell to call bcp.exe to generate an output file. During a busy day it can be called a couple of hundred times. All of a sudden it will stop and I could never find out why. The only thing that helped was to restart the SQL and SQL reporting service. This was not ideal. I shall now explore the heap memory expansion option.