--功能说明：缩小数据库日志文件 --作者：不详 --e.g:数据库名为abc -------------------------------------------------------------- set nocount on declare @logicalfilename sysname, @maxminutes int, @newsize int use abc---要操作的数据库 select @logicalfilename = 'abc_log',---日志文件名 @maxminutes = 10,---limit on time allowed to wrap log. @newsize = 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 @count int, @starttime datetime, @trunclog varchar(255) select @starttime = getdate(), @trunclog = 'backup log '+db_name()+' with truncate_only' dbcc shrinkfile (@logicalfilename,@newsize) exec(@trunclog) --wrap the log if necessary while @maxminutes >datediff(mi,@starttime,getdate()) --time has noe expired and @originalsize =(select size from sysfiles where name = @logicalfilename) and (@originalsize*8/1024)>@newsize begin --outer loop. select @count = 0 while((@count<@originalsize/16) and (@count<50000)) begin -- update insert dummytrans values ('fill log') delete dummytrans select @count = @count + 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
