Delete files olde than 48 hours based on filename, not date modified.

  • Hi,

    Every hour i have files that get copied from one location to another and renamed with adding a date and time to the file name (FileName_200801281130.txt).

    I need to be able to delete files that are older then 48 hours. i looked at the previous posts and there are only refer to deleting the files older then x number of day, but i need hours...

    Please help! 🙂

  • If you're doing this in T-SQL, the only way I know of to do this is use xp_CmdShell to insert a list of the file names into a temp table, parse out the date and time from the file name, then create a dynamic SQL command using xp_CmdShell to delete each file.

    Since xp_CmdShell is a security hole all by itself, I can't recommend using that method. I think you'd be better off using a language that's designed to work with the file system directly, as opposed to T-SQL. (This post is in the T-SQL forum.) I haven't programmed in VB for a while, but I'm pretty sure it could do all that pretty efficiently.

    Even an SSIS package would be safer than xp_CmdShell.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I agree with GSquared. While it is possible to do this via xp_cmdshell, I would not advise it. Create a vbscript or .bat file that will do the same thing, but you can scedule it via the windows sceduler.

  • Hi guys,

    I came up with this code. and it works:)

    declare @d as varchar (8)

    declare @d1 as varchar (12)

    DECLARE @NewFileName VarChar(30)

    DECLARE @SQL_Command VarChar(200)

    DECLARE @SQL_Command1 VarChar(200)

    DECLARE @SQL_Command2 VarChar(200)

    DECLARE @SQL_Command3 VarChar(200)

    DECLARE @BACKUP_DATE as VARCHAR(64)

    DECLARE @BACKUP_DATE1 as VARCHAR(64)

    DECLARE @BACKUP_DATE2 as VARCHAR(64)

    SET @BACKUP_DATE = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())

    as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate())

    as varchar(4))))+cast(day(getdate()) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)

    -- + SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

    --print @BACKUP_DATE

    SET @BACKUP_DATE1 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())

    as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-3)

    as varchar(4))))+cast(day(getdate()-3) as varchar(4))

    --+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)

    --+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

    SET @BACKUP_DATE2 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())

    as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-2)

    as varchar(4))))+cast(day(getdate()-2) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)

    --+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

    --print @BACKUP_DATE1

    --xp_cmdshell 'FORFILES /p w:\Backups\ESSBASE /m *.* /s /c "CMD /C del @FILE" /d -2'

    SET @SQL_Command = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE1 + '*.bak /s /c "CMD /C del @FILE"'

    SET @SQL_Command1 = 'xp_cmdshell ''' +@SQL_Command+''''

    print @SQL_Command1

    EXEC (@SQL_Command1)

    SET @SQL_Command2 = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE2 + '*.bak /s /c "CMD /C del @FILE"'

    --print @SQL_Command

    SET @SQL_Command3 = 'xp_cmdshell ''' +@SQL_Command2+''''

    print @SQL_Command3

    EXEC (@SQL_Command3)

    -----------------

    I have anothe issue now. I have tried to do the same with xp_delete_file and i can not make it work. I will probably add a separate post for this issue:)

  • Well - now that you have SQL server doing your file maintenance - any chance you can figure out how it can turn the coffee on in the morning? :D:P;):w00t::cool::hehe:

    Now THERE would be a feature....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Since you handled the maintenance part, here's a little SSIS app that actually watches folders for changes and returns file names. It allows you to set a wait period as well. Not sure if it will help you, but you can check it out at: http://www.sqlis.com/23.aspx

  • Matt Miller (1/30/2008)


    Well - now that you have SQL server doing your file maintenance - any chance you can figure out how it can turn the coffee on in the morning? :D:P;):w00t::cool::hehe:

    Now THERE would be a feature....

    Heh... POKE the game port to fire an opto-coupled TRIAC...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I agree with the posts that say you should use a programming language to do this. But if you don't have programming software and have to use SQL Server 05 and you have SSIS at your disposal (ships with SQL 05), then I'd use this tool to do this. It's meant for it and probably only take 3 or 4 SSIS tasks to do what you need to do.

    You can also deploy your SSIS package to a server and schedule a job to run this every hour as well.

    Thanks,

    Strick

Viewing 8 posts - 1 through 7 (of 7 total)

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