Delete files older than n-days via T-SQL

  • Comments posted to this topic are about the item Delete files older than n-days via T-SQL

    Your friendly High-Tech Janitor... 🙂

  • The 'Forfiles' command can do this in one line of code. I don't know why we have to re-invent the wheel here.

  • Forfiles is not included in every os by default. I needed to make sure that the code was supportable with no additional external software requirements (resource kit executables and such).

    The Forfiles command would make life quite a bit simpler and it could be easily added to it and the FOR command removed...

    Your friendly High-Tech Janitor... 🙂

  • Thanks for the script.

    Months ago I was looking for a scritp like this one with an additional feature: recursively delete old files in the selected folder an sub-folders.

    I suppose it is difficault to deal with sub-folders at any level using T-SQL and finally I use an external exe program written in C# (deleteold.exe from codeproject).

    Anyway, I would prefere this functionality in T-SQL 😉

    Regards

  • Just curiuos if anyone is using this? I'm planning to implement this at a disaster recovery site. On our live SQL Server we back up the db and ftp it to another location. I figure that I can run this as a job at the DR site to delete the old backups.

  • The code presented is actually about six months older than the date on it - I've been using this method on around 150 servers since it's inception with no issues.

    Edit:

    FWIW, you can add a PRINT @DynDelete and if you schedule the code through SQLAgent you'll have the delete command output so you can record what is being deleted by having the job step append its output to a file. [My production version of this logs the deletes to a table]

    Your friendly High-Tech Janitor... 🙂

  • thanks

  • If you want to use SQL instead of other applications and you're using SQL Server 2005, why you don't use the extended procedure xp_delete_file?

    Personally I feel it quite restricted and I still use a delete command line that I create for myself when I was using SQL Server 2000.

  • I'm trying it and getting all kinds of incorrect syntax error when parsing it.

  • It might be the extra characters that are 'added' to the script when posted.

    Your friendly High-Tech Janitor... 🙂

  • What 'added' characters?

  • The scriptbox appears to add characters (or uses a diff charset) for tabs.

    What errors are you getting?

    Edit:

    If you are indeed only parsing it and not compiling it, it will generate errors as the temptables are not created if you only parse it (a very annoyying feature).

    Your friendly High-Tech Janitor... 🙂

  • I get the same errors either way.

    Msg 170, Level 15, State 1, Line 2

    Line 2: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 41

    Line 41: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 59

    Line 59: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 65

    Line 65: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 73

    Line 73: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 77

    Line 77: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 80

    Line 80: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 89

    Line 89: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 101

    Line 101: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 117

    Line 117: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 126

    Line 126: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 127

    Line 127: Incorrect syntax near '?'.

    Msg 170, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 133

    Line 133: Incorrect syntax near '?'.

  • As I suspected...the tabs are being converted in the scriptbox when submitted.

    I'm not sure what to tell you except to do a search & replace on the tab character. It fails for me with the same errors and it's defenitly the tabs being incorrectly converted when scripts are submitted.

    Your friendly High-Tech Janitor... 🙂

  • ForFiles does'nt support UNC paths, that is why i am trying to use this code 🙂

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply