Delete Files in Shared Location/SAN

,

As a DBA when we have lots of shared location and ForFiles does not work and throws the below error 

"

ERROR: UNC paths (\\machine\share) are not supported.

"

The procedure will be helpful to delete the files. The procedure needs to be run by providing the below details.

exec DeleteOldFiles @path= '\\sharelocation$\servename\transactional_logs\', 
@days = -15
To extract the date, sbustring function is used in the procedure. Please make changes as necessary. In my case, I had the DatabaseName_Datetime.trn
e.g. MYDatabase_20160905080054.trn
if object_id('DeleteOldFiles','P') is not null
	drop procedure DeleteOldFiles;
go

create procedure DeleteOldFiles
(
	@path			varchar(2000),
	@days			int
)
as
begin

	set nocount on

	declare @dircommand		varchar(2000),
			@FileName		varchar(1000),
			@count			int

	if object_id('tempdb..#FullFileList','U') is not null
		drop table #FullFileList;
		
	create table #FullFileList
	( 
		FileNames	varchar(1000),
		CreatedDate varchar(20)
	)

	if object_id('tempdb..#FilesToDelete','U') is not null
		drop table #FilesToDelete;
	create table #FilesToDelete
	(
		Id	int	identity(1,1),
		FileNames	varchar(1000)
	)


	select @dircommand = 'dir '
						+ @path 
						+ ' /b '
	insert into #FullFileList (fileNames)
	exec xp_cmdshell @dircommand


	update	#FullFileList
	set		createdDate = left(substring(filenames , (charindex('.',filenames)-14) ,charindex('.',filenames)),8)


	insert into #FilesToDelete
			(
				FileNames
			)
	select	FileNames
	from	#FullFileList
	where	datediff(dd,getdate(),convert(datetime,createdDate,102))< @days
	and		FileNames is not null

	select	@count = count(1) 
	from	#FilesToDelete

	while(@count >= 1)
	begin
		
		select @FileName = FileNames
		from	#FilesToDelete
		where	Id = @count
		
		select	@dircommand = 'del '
							+ @path 
							+ @FileName
		
		exec master..xp_cmdshell @dircommand
		
		select @count = @count -1 
	end
end;

Rate

Share

Share

Rate