Technical Article

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
(
@pathvarchar(2000),
@daysint
)
as
begin

set nocount on

declare @dircommandvarchar(2000),
@FileNamevarchar(1000),
@countint

if object_id('tempdb..#FullFileList','U') is not null
drop table #FullFileList;

create table #FullFileList
( 
FileNamesvarchar(1000),
CreatedDate varchar(20)
)

if object_id('tempdb..#FilesToDelete','U') is not null
drop table #FilesToDelete;
create table #FilesToDelete
(
Idintidentity(1,1),
FileNamesvarchar(1000)
)


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


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


insert into #FilesToDelete
(
FileNames
)
selectFileNames
from#FullFileList
wheredatediff(dd,getdate(),convert(datetime,createdDate,102))< @days
andFileNames is not null

select@count = count(1) 
from#FilesToDelete

while(@count >= 1)
begin

select @FileName = FileNames
from#FilesToDelete
whereId = @count

select@dircommand = 'del '
+ @path 
+ @FileName

exec master..xp_cmdshell @dircommand

select @count = @count -1 
end
end;

Rate

Share

Share

Rate