Job to Delete folder older than X days ?

Question

Post reply

Job to Delete folder older than X days ?

jbalbo

SSCertifiable

Points: 7583
More actions
February 6, 2014 at 1:23 pm

#304092

Hi
I wanted to run a job nightly that would delete folders within a folder that where older than 3 days old
for example c:\output has 2 folders (c:\output\folder1 and c:\output\folder2) I don't care whats in them only if folder1 or folder2 is older than 3 days delete the entre folder
just leave c:\output
Thanks in Advance
Joe

Viewing 4 posts - 1 through 3 (of 3 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here's an article that could help with that

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/87652/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

This isn't pretty by any means, and you probably should just use a power shell script or something similar, however this will work - just tweak it as needed: SET NOCOUNT ON

DECLARE @Path varchar(75 ), @DeleteDate DATETIME

DECLARE @MyQuery nvarchar (1000)

DECLARE @idx int

DECLARE @Cmd nvarchar (256)

DECLARE @Folder varchar (50)

IF NOT EXISTS (SELECT 1 FROM sysobjects WITH(NOLOCK) WHERE uid = user_id() AND type = 'U' AND name LIKE '#DirectoryList%')

BEGIN

CREATE TABLE #DirectoryList (

Col1 varchar(1000) NULL)

END

IF NOT EXISTS (SELECT 1 FROM sysobjects WITH(NOLOCK) WHERE uid = user_id() AND type = 'U' AND name LIKE '#ParsedFolderList%')

BEGIN

CREATE TABLE #ParsedFolderList (ID int PRIMARY KEY IDENTITY(1, 1) NOT NULL,

[Folder] varchar(255) NOT NULL, CreateDate datetime NOT NULL)

END

/* ######################################### START MAIN PROCEDURE HERE ########################################## */

SELECT @Path = 'C:\Test\', @CMD = 'dir ' + @Path + '', @DeleteDate = GETDATE()-1

INSERT #DirectoryList

EXEC MASTER .dbo. xp_cmdshell @Cmd

IF (SELECT Col1 FROM #DirectoryList WHERE Col1 LIKE '%File Not Found%') = 'File Not Found'

BEGIN

RETURN

END

DELETE FROM #DirectoryList WHERE COL1 IS NULL

DELETE FROM #DirectoryList

WHERE

COL1 LIKE '%Volume%'

OR COL1 LIKE '%Directory%'

OR COL1 LIKE '%bytes%'

OR COL1 LIKE '%.%'

INSERT INTO #ParsedFolderList ([Folder], CreateDate)

SELECT REPLACE(LTRIM(SUBSTRING(Col1, 40, 1000)), '.zip', '') AS 'FileName1',

SUBSTRING(Col1, 0, 18) AS 'CreateDate'

FROM #DirectoryList

ORDER BY CreateDate

/* Remove files with folder modified date < your cuttoff date */

DELETE FROM #ParsedFolderList

WHERE CreateDate <= @DeleteDate

/* Iterate through the folder list, deleting any files which are older */

WHILE (SELECT COUNT( 1) FROM #ParsedFolderList) > 0

BEGIN

SELECT TOP 1 @idx = ID , @Folder = Folder FROM #ParsedFolderList

SET @MyQuery = 'EXEC master.dbo.xp_cmdshell ''rd /s /q "' + LTRIM (RTRIM(@Path)) + RTRIM (@Folder) + '"''' + ', NO_OUTPUT'

PRINT @MyQuery

EXEC (@MyQuery)

DELETE FROM #ParsedFolderList WHERE ID = @idx

END

DROP TABLE #DirectoryList

DROP TABLE #ParsedFolderList

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Did you try my reply in your cross-post ? (which is generally frowned upon)

http://www.sqlservercentral.com/Forums/Topic1538824-392-1.aspx