有关日志压缩
2024-07-21 02:08:22
供稿：网友
set quoted_identifier off
go
set ansi_nulls off
go
create procedure strink_logspace
as
set nocount on
declare @logicalfilename sysname,
@maxminutes int,
@newsize int
select @logicalfilename = rtrim(name),
@maxminutes = 10, -- 最大执行时间
@newsize = 10 -- 最小空间
from sysfiles where status & 0x40 = 0x40
-- setup / initialize
declare @originalsize int
select @originalsize = size -- in 8k pages
from sysfiles
where name = @logicalfilename
select db_name() +'日志原始大小' +
convert(varchar(30),@originalsize) + ' pages/ 8k 或 ' +
convert(varchar(30),(@originalsize*8/1024)) + 'mb'
from sysfiles
where name = @logicalfilename
create table dummytrans
(dummycolumn char (8000) not null)
-- wrap log and truncate it.
declare @counter int,
@starttime datetime,
@trunclog varchar(255)
select @starttime = getdate(),
@trunclog = 'backup log ['+ db_name() + '] with truncate_only'
-- try an initial shrink.
dbcc shrinkfile (@logicalfilename, @newsize)
exec (@trunclog)
-- wrap the log if necessary.
while @maxminutes > datediff (mi, @starttime, getdate()) -- time has not expired
and @originalsize = (select size from sysfiles where name = @logicalfilename) -- the log has not shrunk
and (@originalsize * 8 /1024) > @newsize -- the value passed in for new size is smaller than the current size.
begin -- outer loop.
select @counter = 0
while ((@counter < @originalsize / 16) and (@counter < 50000))
begin -- update
insert dummytrans values ('fill log') -- because it is a char field it inserts 8000 bytes.
delete dummytrans
select @counter = @counter + 1
end -- update
exec (@trunclog) -- see if a trunc of the log shrinks it.
end -- outer loop
dbcc shrinkfile (@logicalfilename, @newsize)
select db_name() +'日志最后大小' +
convert(varchar(30),size) + ' pages/ 8k 或 ' +
convert(varchar(30),(size*8/1024)) + 'mb'
from sysfiles
where name = @logicalfilename
drop table dummytrans
print '*** 数据库日志压缩成功 ***'
set nocount off
go
set quoted_identifier off
go
set ansi_nulls on
go
--used
exec strink_logspace
国内最大的酷站演示中心!
