December 20, 2005 at 9:42 am
Hello all,
I'm running a SQL job on SQL 2000 / Win2000 Server that's started acting odd in the last couple of weeks. Normally the job is launched as a step within another job and only runs about 3-6 seconds. All it does is exec an application and it's worked fine in the past until recently. In other words:
1. A multi-step job starts, 2. step 2 launches another job, 3. that job launches an application which runs for about 3-6 seconds.
But now, when the job which executes the application is started, it won't stop until I cancel it. I was away on vacation when this started and I got back to find it had been running for 134 hours. It doesn't matter if I invoke the job or if it's triggered by the SQLAgent account, same result. The application is executed using the xp_cmdshell command, but I haven't had any issues with that until now. The application works fine if I log into the server and double-click it to execute it. Nothing has changed in the SQL or Windows/AD security settings. And we rebooted the server last night just in case that was it. Has anyone had an issue like this?
Thank you!
December 20, 2005 at 9:45 am
Same result when you execute the xp_cmdshell manually, say through Query Analyzer?
K. Brian Kelley
@kbriankelley
December 20, 2005 at 10:04 am
Yep, same results in QA regardless of the credentials used, whether using xp_cmdshell or 'exec sp_start_job...'
December 20, 2005 at 11:15 am
What is the job that is failing? Is it trying to do a backup? If so, could the backup file be in use at the time? Could it be trying to go to a specific path and the path be unavailable or access has changed?
-SQLBill
December 20, 2005 at 12:47 pm
The job that's failing is the one descibed above that executes an application using xp_cmdshell. The path is still valid, access has not changed, and the application works fine when manually double-clicked. It just doesn't work through a SQL job or in QA.
All the job does is execute the application. The application has no user intervention required (no dialogs or messages). All it does is scan through a folder full of text files looking in the text files for the word "error". If it finds "error" in any of the files it sends an email to me.
From looking around this board and other places I'm getting the general feeling that xp_cmdshell isn't a very reliable command; kind of buggy if I'm understanding everyone right. Would it be better to use the CmdExec job type and just use DOS commands to launch the program? I haven't tried that yet as I'm not very familiar with SQL jobs, but I can figure out just about anything. Someone else set up all of this stuff I'm trying to troubleshoot, but I haven't been able to figure out why this job is acting the way it is.
Thanks!
December 21, 2005 at 2:50 am
Has the number of text files or size thereof increased recently?
What happens if you invoke the application manually from the OS, not from SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2005 at 9:27 am
xp_cmdshell & CmdExec are basically the same thing. You're shelling out to a command shell on both cases. However, if the application doesn't do a good job of error handling and there is an issue with one of the files (for instance, the account trying to access the file doesn't have rights to do so), you could run into a problem. In both cases the shell is completely reliant it on the application to bubble an error up. If it doesn't, the shell doesn't know anything is wrong.
I saw this myself recently in a production job at my organization. It was IO related, not permissions related, but the app basically timed out without bubbling up an error. Our job scheduling system (which had a call to shell out just as you would with xp_cmdshell or CmdExec) didn't get an error back and marked the job step as completing successfully. This led to problems down the chain.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply