September 13, 2012 at 4:36 pm
I would like to create a sql agent job to run operating system cmd, as a step for a job.
I tried to delete the files in a log directory that is older than 5 days,
I tried below, but it is not working.
forfiles -p "C:\SSISFILES\Log\" -s -m *.txt -d -5 -c "cmd /q /c del C:\SSISFILES\Log\"
I also don't want the prompt to confirm delete, How can I do that? Thanks
September 14, 2012 at 1:18 am
Please try below one
EXEC xp_cmdshell 'FORFILES /p "C:\SSISFILES\Log\" /s /m *.txt /d -4 /c "CMD /C echo Deleting the file : @file del /Q /F @FILE && del /Q /F @FILE " '
*******:cool:
Sudhakar
September 14, 2012 at 6:00 am
i use a simple vb script, works for me
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 14, 2012 at 7:18 am
I'm going to make the presumption that the command you gave is in a batch file being called. That said, it's an easy fix.
Change this:
forfiles -p "C:\SSISFILES\Log\" -s -m *.txt -d -5 -c "cmd /q /c del C:\SSISFILES\Log\"
To this:
forfiles -p "C:\SSISFILES\Log\" -s -m *.txt -d -5 -c "cmd /q /c del /q C:\SSISFILES\Log\"
Add a "/q" after the DEL will tell it to run in "quiet mode." Or, as stated when doing "del /?" at a command prompt:
Quiet mode, do not ask if ok to delete on global wildcard
That should straighten it out.
Edit: Just re-looked at the other posts, and Sudhakar has the /Q as well in his delete, which should also work.
September 14, 2012 at 9:25 am
Thank you all.
I would like to add the command in sql agent job, using operating system(CmdExec).
can I use the command directoy in the code window instead of using a bat file?
What is the syntax then? And can I just use cmd, instead of EXEC xp_cmdshell which i have to enable it (would rather not for saftey purpose).
Thanks
September 14, 2012 at 10:57 am
More help please
September 14, 2012 at 12:33 pm
Instead of using cmd exec - use Powershell with the following:
Get-ChildItem C:\SSISFILES\Log | Where {$_.LastWriteTime -lt (Get-Date).AddDays(-5)} | Remove-Item -Force
If you want this to recurse through sub-directories and delete files, this will work:
Get-ChildItem C:\SSISFILES\Log -Recurse | Where {!$_.PsIsContainer -and $_.LastWriteTime -lt (Get-Date).AddDays(-5)} | Remove-Item -Force
If you want to see what would happen, but don't want it to actually remove anything - add the -WhatIf parameter, as in:
Get-ChildItem C:\SSISFILES\Log | Where {$_.LastWriteTime -lt (Get-Date).AddDays(-5)} | Remove-Item -Force -WhatIf
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 14, 2012 at 1:13 pm
Thanks, this work perfect.
I do want to delete subfolders, so the second statement works too.
How can I only delete files with extension .txt in above command
Thanks
September 14, 2012 at 1:28 pm
Add another criteria to the where statement in the second pipeline:
Get-ChildItem C:\SSISFILES\Log -Recurse | Where {!$_.PsIsContainer -and $_.LastWriteTime -lt (Get-Date).AddDays(-5) -and $_.Extension -eq ".txt"} | Remove-Item -Force -WhatIf
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 14, 2012 at 1:59 pm
This works perfect, thank you!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply