alter proc usp_delete_log (@path varchar(120),@dbname varchar(32), @daynum smallint) 
-- usp_delete_log 'E:\progect\LOG','pubs',5   总是保持最近5天以内的日志
-- 日志格式为 pubs_log_20060505.log
-- 下为备份过程
/*create procedure usp_log_backup(@path varchar(120),@dbname varchar(32)) 
as
    declare @curtime char(10)
    declare @cmdstr varchar(120)      
    set @curtime=select convert(char(13),getdate(),112)  ----20070319      
    set @cmdstr=' backup  log '+@dbname+' to disk ='''+@path+'\'+@dbname+'_log_'+@curtime+'.log'''+' with init'
    execute(@cmdstr)*/
as
    begin tran
    declare @curtime varchar(20)
    declare @cmdstr1 varchar(120)
    declare @cmdstr2 varchar(120)  
    declare @filename varchar(80)   
    CREATE TABLE #fname1  ( fname varchar(80))
    CREATE TABLE #fname2  ( fname varchar(80))
    set @curtime=convert(char(8),cast(datediff(dd,@daynum,getdate()) as smalldatetime),112)---20061001
    set @cmdstr1=' exec master.dbo.xp_cmdshell ''dir '+@path+'\'+@dbname+'*.log/b'''
    INSERT #fname1
    execute(@cmdstr1)
    insert #fname2
    select * from #fname1 where cast(left(right(fname,12),8) as int)<cast(@curtime as int) 

    declare tb cursor for
    select fname from #fname2 where fname is not null
    declare @fname varchar(80)
    open tb
    fetch next from tb into @fname
    while @@fetch_status=0
    begin
       set @cmdstr2='exec master.dbo.xp_cmdshell ''del '+@path+'\'+@fname+''''
       exec(@cmdstr2)
       fetch next from tb into @fname
    end
    close tb
    deallocate tb
    drop table #fname2
    rollback tran