I need to run a job through SQL Server Agent. A job that deletes obsolete files from the application server based on retention days (obsolete physical files, files which are not stored in MS SQL). Where should I begin with? Does the job have to be a stored procedure?
Thanks!
why do you want to do this in MS SQL ?
a simple batch file will do the job.
Hi khtan,
To comply with the coding standards and our backend jobs are consolidated in sql server agent and will be scheduled to run on a certain time of day. Is it possible to create a batch file and run it through ms sql server agent? Thanks 
yes. you can
you batch file can be something like this.
The command below will delete *.bak that is more than 7 days
Forfiles /P C:\BACKUP /M *.bak /D -7 /C "cmd /c DEL /q @path"Hi khtan,
Thanks for your reply. Btw, i will not be dealing with .bak files. I have to housekeep a certain folder's content based on retention days. My files have timestamps.
timestamps on filename ? So the housekeeping is based on file name rather than file's date & time ?
Sorry for the confusion khtan. Yes it will be based on file's date and time.
forfiles -p c:\pathtofiles\ -m *.int-d -5 -c "cmd /c del @path"
5 days or older in this case right? will this syntax be fine?
should be
Forfiles /P C:\PATHTOFILES /M *.int /D -5 /C "cmd /c DEL /q @path"
you can refer to here for forfiles help
https://technet.microsoft.com/en-us/library/cc753551.aspx
what about:
Forfiles /P C:\PATHTOFILES /D -5 /C "cmd /c DEL /q @path"
dropping the /M *.int will delete any file type? that is more than 5 days?
Yes. the default mask is * . *
Thanks for the info.
Forfiles /P C:\PATHTOFILES /D -5 /C "cmd /c DEL /q @path"
-will this be the only line of code that I need? and then I can integrate it to MS SQL?
do you have any idea where I can test this code?
Forfiles /P C:\PATHTOFILES /D -5 /C "cmd /c DEL /q @path"
Apologies for too much questions, im very new with ms sql and batch files.
you can execute that under Windows command prompt. make sure you are testing it on a temp folder and not your actual folder
Hi khtan,
Thanks, it worked on my local PC when i ran it in cmd. The housekeeping job will be deployed and scheduled in SQL Server Agent(ServerA) and I need to housekeep folders on the Application Server (ServerB). I tried to test the code and used this code:
Forfiles /P \ServerB\PATHOFFILES /D -5 /C "cmd /c DEL /q @path" but it doesnt work, its says "ERROR: UNC paths (\machine\share are not supported)" any idea?
Looks like Forfiles does not support UNC paths
or maybe the server agent's credential does not have access to that path ?
any other alternatives for forfiles?
they say pushd will work, but i cant seem to make this work:
pushd \ServerB\D:\Folder Forfiles /P "\ServerB\D:\Folder " /D -5 /C "cmd /c DEL /q @path" popd
i am not sure what is pushd . .
Are there any alternatives that will remotely housekeep files older than N days on schedule from MS SQL (with SQL Server Agent Job) ?