This scripts builds up on the idea of using ForFiles. See

http://www.cookingsql.com/2014/12/batch-file-to-remove-old-files/

/*
DESCRIPTION:
Creates a JOB to delete files of a certain age on a folder, based on EXTENSION
 
CONFIGURATION
Plenty
SET @days = 30   for the age of the file, in days
SET @NAME = 'IBM_Cleanup'  for the name of the job
SET @ext = 'txt'  for the extension of the files to delete.

The DEFAULT FOLDER is the location for your ERRORLOG folder, but it can be customized

After running the script, you still need to schedule the job

Author: Miguel Quintana

LIMITATION:
It does not do subfolders

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
*/

 

 
declare @cmd nvarchar (1000)
DECLARE @sqlerrorlog TABLE (logdate DATETIME, processinfo SYSNAME, logtext VARCHAR(MAX))
DECLARE @path VARCHAR(300)
DECLARE @LOG_FOLDER varchar(300)
DECLARE @days varchar(4)
DECLARE @NAME varchar(100)

DECLARE @ext varchar (4)


/*
This section sets the ErrorLog folder as destination to delete file.
If you would like to set your own path, comment out this section.
and use the  SET @LOG_FOLDER  section
 
 
*/
SET @days = 30
SET @NAME = 'IBM_Cleanup'
SET @ext = 'txt'


--/*
INSERT INTO @sqlerrorlog EXEC xp_readerrorlog 0, 1
SELECT @path=logtext FROM @sqlerrorlog WHERE logtext LIKE '%Logging SQL Server messages in file%'

SET @LOG_FOLDER= SUBSTRING(@path, CHARINDEX( '''', @path) + 1,LEN(@path)-CHARINDEX( '''', @path) - CHARINDEX( '\', REVERSE(@path)))
-- */
 
-- only use this line below if you do not want the errorlog as your default folder
-- SET @LOG_FOLDER = 'D:\folder_here'
 
 
/*  DO not change below this line */
 

set @cmd=N'cmd /q /c "For /F "tokens=1 delims=" %v In (''ForFiles /P "'+@LOG_FOLDER+'" /m *.'+@ext+' /d -'+@days+' 2^>^&1'') do if EXIST "'+@LOG_FOLDER+'"\%v echo del "'+@LOG_FOLDER+'"\%v& del "'+@LOG_FOLDER+'"\%v"' 

 
--print @cmd
 
USE [msdb]
 
 
/****** Object:  Job [IBM_CommandLog Cleanup]    Script Date: 04/02/2014 14:16:35 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 04/02/2014 14:16:35 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@NAME,  
        @enabled=1,  
        @notify_level_eventlog=2,  
        @notify_level_email=0,  
        @notify_level_netsend=0,  
        @notify_level_page=0,  
        @delete_level=0,  
        @category_name=N'Database Maintenance',  
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [output_text]    Script Date: 04/02/2014 14:16:35 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'output_text',  
        @step_id=1,  
        @cmdexec_success_code=0,  
        @on_success_action=1,  
        @on_success_step_id=0,  
        @on_fail_action=2,  
        @on_fail_step_id=0,  
        @retry_attempts=0,  
        @retry_interval=0,  
        @os_run_priority=0, @subsystem=N'CmdExec',  
        @command=@cmd,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
 
GO
 
 

FORFILES -p "Path to the folder" -s -m *.txt -d   -c "cmd /c del @path"

REM  Delete all BAK files from folder C:\backup  that are 5 days or older

FORFILES -p "C:\backup" -s -m *.bak -d -5 -c "cmd /c del @path"

Want a job to do this automatically? See

http://www.cookingsql.com/2014/12/sql-job-to-delete-old-files/

Sometimes it's necessary to delete files based on age, and we can use a VB Script to do that:

'On Error Resume Next ' < -- Comment out for troubleshooting.

Const strPath = "C:\SQL_Backups\SOG-NADC-SP-DB1" ' Update this parent folder.
Const iDaysOld = 2   

' PURPOSE:	This script will delete all files recursively starting from
'			strPath older than 2 days and empty subfolders.
'			Very convenient for cleaning temp and log files.
' WARNING:	Please understand the risks of using this script.
' WARNING:	DO NOT MODIFY BELOW UNLESS YOU KNOW WHAT YOU ARE DOING.

Dim objFolder, objSubFolder, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getFolder(strPath)

Call Search(objFolder)

Sub Search(objFolder)
	For Each objFile In objFolder.files
		If dateDiff("d",objFile.dateLastModified,Now) > iDaysOld Then objFile.Delete
	Next
	For Each objSubFolder In objFolder.SubFolders
		Search(objSubFolder)
	Next

End Sub

'  end of file