July 20, 2009 at 8:02 am
I have a nightly backup job that performs several tasks. One of those tasks is to use xp_cmdshell to delete old backup files and old script files to be replace with the newer backups and scripts. The procedure simply creates a command such as "del C:\myscript.sql" then executes this command.
This has been running fine for a few years and it is still running fine on all servers except one. I have talked to our infrastructure guys about any changes they may have made and, of course, the answer is "we haven't done anything". So, I have been looking and looking.
The server was rebooted this weekend and I was able to run the following as a test and it worked:
EXEC xp_cmdshell 'dir c:\*'
I then tried to run the following:
EXEC xp_cmdshell 'del c:\myscript.sql'
and it deleted the file but then just hung.
I have searched over and over for log entries or anything that might tell me what is going on. I realize a command that is expecting user interaction will hang with xp_cmdshell but one, this has worked for years, and two, I tested it, just in case, by opening a command line and running the del command and it worked just fine and returned right back to the prompt.
Anybody got any ideas about how I can trace down this problem?
Thanks.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
July 20, 2009 at 9:18 am
Can you logon to that server either at the console or remote desktop and run that very same command? If not then you could specify the "/Q" option on the del command, this forces it to work without user input, and if it can't then it fails. But you don't get hung up on "Are You Sure (y/n)?" messages.
Also, because I don't like errors I almost always build the script to say:
IF EXIST C:\Pathtofile\FileName.ext DEL C:\Pathtofile\FileName.ext
But if you have dynamic files that are always changing and you know the file will always exist, that kind of checking can be dispensed with..
CEWII
July 20, 2009 at 9:24 am
Yeah, I logged on to the server and ran the exact command that was created in the script because I was afraid maybe some kind of OS change might have made the DEL command work a little differently. The target file does get deleted, its just that the xp_cmdshell command won't ever finish after that.
Now I've got another problem because, even though I killed the process running the command, the KILL will not complete. The only way to take care of that is going to be restarting the server.
This stinks because this is a heavily utilized production server so my windows to work on it are very limited.
Thanks, anyway.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
July 20, 2009 at 10:08 am
Be sure you log on as the SQL Agent account to check things. It's possible that there was a security difference.
The other thing to look at is could the job have gotten hung because the file was in use?
July 20, 2009 at 10:57 am
Thanks Steve but, yes, I have logged in as the service account and the command line works fine. The file is also not in use.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
July 20, 2009 at 11:03 am
Very strange. I might look at the Process utility from sysinternals and see if you can get some clue about process might be stuck there.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply