CREATE
PROCEDURE
[
dbo
]
.
[
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 ' *** 数据库日志压缩成功 *** '
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 ' *** 数据库日志压缩成功 *** '