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