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