Stuck file with WinSCP script and OpenSSH on Windows 2000 Server

A company I do some business with has a WinSCP script that uploads a file nightly using SFTP, overwriting it if it's there.  They also have another similar script that transfers the file to a different server and deletes the original file.   (I think the machines are running Windows 2000 Server, with OpenSSH to provide SFTP functionality.)

Unfortunately, at some point, Windows said that the file was in use, and it couldn't be deleted.  Attempting to delete it manually gave a File In Use error.    Hence, both scripts failed to function properly.

However, after I ran a WinSCP script to upload the file with a different name, the lock on the original file was mysteriously removed, and I was able to delete any remnants of the file.

Therefore, I suspect that WinSCP or OpenSSH somehow had a handle to the file that was released by running another WinSCP script successfully.

This is the first time that happened with these scripts, which had been running for years without ever encountering this specific problem before.  Nevertheless, I would be interested in any ideas about how to prevent this sort of thing in the future.  Obviously, one could have a script that keeps changing the filename (e.g., appending 1, 2...) if it gets a "file in use" error.  But I wonder if there's a simpler or more elegant/direct way.
NewbisAsked:
Who is Participating?
 
NewbisConnect With a Mentor Author Commented:
I think the reason why it got "stuck" was that suddenly it need to accept hostkey (similar to http://winscp.net/forum/viewtopic.php?t=5883).  This appears to be what caused the problem, and I guess only error handling and alerting will solve that.
0
 
BigRatConnect With a Mentor Commented:
As a general principle I never delete data until it is REALLY old.

I had a similar problem recently. Someone unloaded and deleted data from one database into a file, transfered the file to a second machine which then loaded the data into another database. Every night. Naturally one night the data did not get into the second machine, on that night the processing was not performed, so the next night's data went in, overwriting the file, and got processed producing many errors because the previous night's data was missing.

So, the data will be unloaded BUT NOT deleted from the first machine. After the unload the data in the database will be marked as "transfered". We can undo this an re-export the data if things go wrong. The unload operation is not done on a per date basis, but all records which are not marked "transfered" are unloaded.

The file gets named with the date. It gets passed over to the other machine and loaded into the database. At the same time a record will be written with the date denoting a successful load. The processing will fail to start unless the latest date record is equal to yesterday's date (with alloances for Suindays and holidays). records are processed and marked as such they are not deleted. It is therefore possible to recover all the data at any time if anything goes wrong.

So in your case one starts by naming the file with today's date. It never gets deleted (except possibly at year's end and then all file which are older than say three months.
0
 
AlexPaceConnect With a Mentor Commented:
It sounds like your script needs some error handling.

Have it check the result code and automatically retry the transfer if it fails.  If it fails again you could do some other operation to attempt to unlock it, maybe upload a junk file or change directories, or log out and back in, or something like that.  Then, if everything fails, have it send an email to alert of the problem so the next day's data doesn't overwrite like BigRat mentioned above.

I'm not sure if WinSCP script can do this but if not you could buy Robo-FTP to do it.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
NewbisAuthor Commented:
Thanks for the comments.  My situation is actually a bit different from BigRat's.  The file only gets deleted after it is copied; if for some reason it got deleted when it shouldn't, that wouldn't be a big deal...nothing would get lost that doesn't exist somewhere else.  Mainly, it must be deleted because even though there's no anonymous access to the directory and only authorized users can get it, we still don't want to have the data lying around when it doesn't need to be there.  As I mentioned, I could of course put in some error-handling if they want me to spend time with it.  Mainly, I just want to understand how and why the file got into a state where it couldn't be deleted (apparently related to WinSCP or the local SFTP server program having continuous "use" of it?) and whether that can be prevented in the first place.
0
 
BigRatCommented:
We have had similar situations where a process has disappeared (perhaps died, at least you can't find it) leaving files open. Obviously by rebooting every day reduces the risk, but many of our customers don't daily reboot.
0
 
NewbisAuthor Commented:
I found what was likely causing WinSCP to cause the file to have an open handle to it.  My intention in asking the question was to figure out what was the causing the problem and potentially how to prevent that, not just how to make the solution more robust in case there's a problem, which I know also needs to be done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.