August 8, 2013 at 6:27 am
Hi All,
Looking for assistance to develop T-SQL code
Requirement is to delete old files from directory & its sub-directories(say F:\Temp\test\), which were placed in directory before 1 week and not modified.The files can be of any type :txt,bak,zip,doc,bak,mdf,ldf
Thanks in advance for any help.
August 8, 2013 at 6:32 am
Is there a particular reason you want to do this in TSQL?
.NET would seem much more fit for the task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2013 at 6:38 am
Thanks for your quick response
I would like to create a SQL Server Agent Job to perform this cleanup activity on a daily basis.So that my server will be fresh and clean for another day:-)
August 8, 2013 at 6:41 am
You could create an SSIS package that uses .NET in a script task.
You can schedule SSIS with SQL Agent.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2013 at 7:36 am
Why not just create a .NET console app and schedule it with windows scheduler? This doesn't like the type of thing you need sql for at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2013 at 10:45 am
Sreejith! (8/8/2013)
Hi All,Looking for assistance to develop T-SQL code
Requirement is to delete old files from directory & its sub-directories(say F:\Temp\test\), which were placed in directory before 1 week and not modified.The files can be of any type :txt,bak,zip,doc,bak,mdf,ldf
Thanks in advance for any help.
You can just create maintenance plan with a cleanup job and use the SQL Agent to schedule it. No T-SQL or any other type of scripting required.
http://technet.microsoft.com/en-us/library/ms177182(v=sql.100).aspx
-- Itzik Ben-Gan 2001
August 8, 2013 at 10:57 am
I use forfiles (google it for the reference on how to use) call from xp_cmdshell. It alows be to delete files older than n days, and you can pass all the parameters via the xp_cmdshell
example forfiles /p C:\Backup\AuditDB /d -5 -S -m *.bak -C "cmd /c del @file
/p = directory
/d = number of days to keep
-m = file extension
-C = the command to do to each file in this case del the file
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
August 8, 2013 at 11:20 am
GregoryF (8/8/2013)
I use forfiles (google it for the reference on how to use) call from xp_cmdshell. It alows be to delete files older than n days, and you can pass all the parameters via the xp_cmdshellexample forfiles /p C:\Backup\AuditDB /d -5 -S -m *.bak -C "cmd /c del @file
/p = directory
/d = number of days to keep
-m = file extension
-C = the command to do to each file in this case del the file
For something like this why not just use Maintenance Cleanup Task? This functionality has been available since as long as I can remember (6.5 or 7.0)... Then there's no need to call xp_cmdshell or need to create any scripts.
-- Itzik Ben-Gan 2001
August 8, 2013 at 11:24 am
Alan.B (8/8/2013)
GregoryF (8/8/2013)
I use forfiles (google it for the reference on how to use) call from xp_cmdshell. It alows be to delete files older than n days, and you can pass all the parameters via the xp_cmdshellexample forfiles /p C:\Backup\AuditDB /d -5 -S -m *.bak -C "cmd /c del @file
/p = directory
/d = number of days to keep
-m = file extension
-C = the command to do to each file in this case del the file
For something like this why not just use Maintenance Cleanup Task? This functionality has been available since as long as I can remember (6.5 or 7.0)... Then there's no need to call xp_cmdshell or need to create any scripts.
The maintenance task has to be set up by hand for each server. You can deploy a script to all servers, and have customizable parameters for each. It takes a long time to build a maintance task, you can deploy the script to multiple servers with a simple multi-server query, and you can add customized parameters to an SP which is easier to modify than the maint task.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
August 19, 2013 at 7:28 am
Thanks All
But the files(txt,mdf,doc,excel,zip) in the 'test' directory doesn't have proper time stamp, which help us to determine whether these files has been placed in the folder for more than 1 week
August 19, 2013 at 7:30 am
Sreejith! (8/19/2013)
Thanks AllBut the files(txt,mdf,doc,excel,zip) in the 'test' directory doesn't have proper time stamp, which help us to determine whether these files has been placed in the folder for more than 1 week
If they lack an accurate datetime stamp for created or modified date, there is not much that you can do.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
August 19, 2013 at 7:55 am
Sreejith! (8/19/2013)
Thanks AllBut the files(txt,mdf,doc,excel,zip) in the 'test' directory doesn't have proper time stamp, which help us to determine whether these files has been placed in the folder for more than 1 week
Garbage in, garbage out :crazy:
You could also use a SQLAgent jobstep with a simple line of Powershell :w00t:
$xDate = (get-date).AddDays(-(5))
Get-ChildItem $Path -Recurse | Where-Object {$_.PSIsContainer} | % {Get-ChildItem $_.FullName | Where-Object {! $_.PSIsContainer -and $_.LastWriteTime -lt $xDate} | Remove-Item}
You will have to all a little filter to only handle the extentions you aim for.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 20, 2013 at 4:09 am
I would second the PowerShell approach.
It's a lot cleaner and dare I say more flexible IMHO.
Scheduling a PShell Script from within Sql Agent is also straight forward as has already been pointed out.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply