缩小SQL Server日志文件的SQL语句
2024-08-31 00:48:00
供稿：网友
把下面的代码粘贴到sql query analyzer（查询分析器）里执行即可（注意修改红色部分的3个参数）：
set nocount ondeclare @logicalfilename sysname,@maxminutes int,@newsize int
use dicky --需要缩小日志的数据库名select @logicalfilename = 'dicky_log', --日志文件的逻辑名，非物理文件名@maxminutes = 10, -- limit on time allowed to wrap [email protected] = 2 --需要重新设定的日志文件的大小(单位:m)，此值必须小于原先文件大小
-- setup / initializedeclare @originalsize intselect @originalsize = size from sysfileswhere name = @logicalfilenameselect 'original size of ' + db_name() + ' log is ' + convert(varchar(30),@originalsize) + ' 8k pages or ' + convert(varchar(30),(@originalsize*8/1024)) + 'mb'from sysfileswhere name = @logicalfilenamecreate 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, @newsize)exec (@trunclog)-- wrap the log if necessary.while @maxminutes > datediff (mi, @starttime, getdate()) -- time has not expiredand @originalsize = (select size from sysfiles where name = @logicalfilename) and (@originalsize * 8 /1024) > @newsize begin -- outer loop.select @counter = 0while ((@counter < @originalsize / 16) and (@counter < 50000))begin -- updateinsert dummytrans values ('fill log') delete dummytransselect @counter = @counter + 1end 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 = @logicalfilenamedrop table dummytransset nocount off
