SQLTeam.com | Weblogs | Forums

SQL server agent job that housekeeps obsolete physical files

tsql
sql2008

#1

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!


#2

why do you want to do this in MS SQL ?

a simple batch file will do the job.


#3

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 :grin:


#4

yes. you can


#5

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"

#6

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.


#7

timestamps on filename ? So the housekeeping is based on file name rather than file's date & time ?


#8

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?


#9

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


#10

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?


#11

Yes. the default mask is * . *


#12

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?


#13

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.


#14

you can execute that under Windows command prompt. make sure you are testing it on a temp folder and not your actual folder


#15

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?


#16

Looks like Forfiles does not support UNC paths

or maybe the server agent's credential does not have access to that path ?


#17

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


#18

i am not sure what is pushd . .


#19

Are there any alternatives that will remotely housekeep files older than N days on schedule from MS SQL (with SQL Server Agent Job) ?


#20

maybe this will help

https://www.petri.com/forums/forum/windows-scripting/general-scripting/24799-deleting-remote-files?t=24241