数据库日志清除

set nocount on
declare @LogicalFileName sysname
declare @MaxMinutes int
declare @NewsSize int
use shopu
select @LogicalFileName='shopu_log.ldf',-----日志文件名
@MaxMinutes=10,---limit on time allowed to warp log.
@NewsSize=1    ---你想设定的日志文件的大小(M)
-- setup / initialize
declare @OriginalSize int
select @OriginalSize=size from sysfiles where name=@LogicalFileName
select 'Original size of'+db_name()+'LOG is'+CONVERT(varchar(30),@OriginalSize)+'8K pages or'+
convert(varchar(30),(@OriginalSize*8/1024))+'MB'
from sysfiles
where name=@logicalFileName
-------
create table DummyTrans
(DummyColumn char(8000) not null)
--------
    declare @Counter int,@StartTime datetime,@TruncLog varchar(255)
 select @StartTime=getdate(),@TruncLog='BACKUP LOG'+DB_name()+'WITH TRUNCATE_ONLY'
 DBCC SHRINKFILE(@LogicalFileName,@NewsSize)
 EXEC(@TruncLog)
----------------------------------------------------
--Wrap the log if necessary
    while (
 @MaxMinutes>DATEDIFF(mi,@StartTime,Getdate())
 and @OriginalSize=(select size from sysfiles where name=@LogicalFileName)
 and (@OriginalSize * 8/1024)>@NewsSize
 )
　 begin
   select @Counter=0
   while((@Counter<@OriginalSize/16) and (@Counter<50000))
   begin       
    insert DummyTrans Values ('Fill log')
    delete DummyTrans
    select @Counter=@Counter+1
　　  end
   exec (@TruncLog)
 end
    select 'Final Size of'+db_name()+'LOG is'+
    convert(varchar(30),size)+'8K pages or'+convert(varchar(30),(size*8/1024))+'MB'
    from sysfiles
    where name=@LogicalFileName
 Drop Table DummyTrans
    set nocount off