删除过期的trace文件

if exists(select * from sysobjects where xtype='P' and name='up_trace_autoDELfiles')
drop procedure up_trace_autoDELfiles
go

create procedure dbo.up_trace_autoDELfiles
@path nvarchar(256), --path must be absolute path
@search_mask nvarchar(256),
@date_limit nvarchar(256)=''

as
/**********
--文件名 :
--用途 :删除过期的trace文件
--输入参数 :
--返回值解释 :
--创建者 : summer.yang
--创建日期 : 2005-6-18
--修改者 :
--修改日期 :
--修改注解 :(引用请保留此信息)
--备注说明 :
**********/
--delete the trace files which are created before one week in the folder "old"
set nocount on
set @path=ltrim(rtrim(@path))
set @search_mask=ltrim(rtrim(@search_mask))
set @date_limit=ltrim(rtrim(@date_limit))
--parameters: please referance the command FORFILES on local windows help
--the command below is used under win2000 platform
declare @return int,@message varchar(8000)
set @return=0
set @message=''
-------
declare @cmd_sql nvarchar(2000)
declare @log_file nvarchar(128)
set @log_file='Del_file_history.txt'

--deleted the moved trace files
if right(@search_mask,1)<>'.' set @search_mask=@search_mask+'.trc'
else set @search_mask=@search_mask+'trc'

if @date_limit <>'' set @date_limit=' -d'+@date_limit

--check the @path

--delete the old file:trace_mvFileList.txt
if right(@path,1)<>'' set @log_file=@path+''+@log_file
else set @log_file=@path+@log_file

--make a file named trace_mvFileList.txt to list moved files
/*--this is windows2003 version
set @cmd_sql='FORFILES '+@recursion_char+' /P '+@path+' /M '+@search_mask+@date_limit
+' /C "CMD /C if @isdir==FALSE MOVE @path '+@destination_path+' && Echo @FILE >> '+@log_file+'"'
--this is windows2000 version
--FORFILES -pE:traceweb58 -mTrace_Web58_DL_20050731A*.trc -c"CMD /C if @ISDIR==FALSE MOVE @FILE E:traceweb58old && Echo @FILE >> E:traceweb58trace_mvFileList.txt"
*/
--FORFILES -pE:traceweb58old -mTrace_Web58_DL_*.trc -d-7 -c"CMD /C if @ISDIR==FALSE DEL /A A @FILE && Echo @FILE >>DEL_file_history.txt

set @cmd_sql='FORFILES -p'+@path+' -m'+@search_mask+@date_limit
+' -c"CMD /C if @ISDIR==FALSE DEL /A A @FILE && Echo @FILE >> '+@log_file+'"'

--debug
--print @cmd_sql
exec @return=master.dbo.xp_cmdshell @cmd_sql,no_output
if @return>1 or @@error<>0
begin
set @message ='Error: DEL files failed.'
if @return=0 set @return=-2
goto finish
end
else if @return=1
begin
set @message='Information: There is no any file fit for search mask.'
set @return=0
goto finish
end
--read files name from trace_mvFileList.txt

--------
finish:
if @return<>0
begin
raiserror (@message,16,1 ) --with log
return @return
end
return @return

GO