Query Help - To delete old files

  • 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.

  • 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

  • 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:-)

  • 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

  • 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/

  • 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

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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!

  • 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_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

    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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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_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

    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!

  • 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

  • Sreejith! (8/19/2013)


    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

    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!

  • Sreejith! (8/19/2013)


    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

    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

  • 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