Pessoal,
Em alguns momentos não consegui diminuir o log com esses comandos. Então achei, não me lembro mais aonde, um script que diminui o log na marra. Esse funcionou. Segue o script abaixo.
DECLARE @LogicalFileName sysname,
@MaxTime tinyint,
@MaxMinutes tinyint,
@NewSize int,
-- Setup / initialize
@OriginalSize int,
-- Wrap log and truncate it.
@Counter int,
@StartTime DateTime,
@StartMin DateTime,
@TruncLog nvarchar(255),
@DB sysname,
@StmtSQL nvarchar(300)
SET NOCOUNT ON
-- Limit on time allowed to wrap log. in MB
SET @MaxTime = 10
SET @MaxMinutes = 1
SET @NewSize = 2
SET @StartTime = GETDATE()
SET @DB = db_name()
SET @TruncLog = N'BACKUP LOG ' + @DB + ' WITH TRUNCATE_ONLY'
-- Use sp_helpfile to identify the logical file name that you want to shrink.
SELECT @LogicalFileName = RTrim(name),
@OriginalSize = size
FROM sysfiles
WHERE GROUPID = 0
SET @StmtSQL = N'SELECT ''Original Size of LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'' AS ' + @DB
Exec sp_ExecuteSQL @StmtSQL
WHILE (@OriginalSize*8/1024) > @NewSize
AND @MaxTime > DATEDIFF(mi, @StartTime, GETDATE())
BEGIN
CREATE TABLE DummyTrans( DummyColumn char (8000) not null )
SET @StartMin = GETDATE()
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
Exec sp_ExecuteSQL @TruncLog
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF(mi, @StartMin, 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.
SET @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
SET @Counter = @Counter + 1
END -- update
PRINT 'FIM 3º LOOP'
Exec sp_ExecuteSQL @TruncLog -- See if a trunc of the log shrinks it.
END -- outer loop
PRINT 'FIM 2º LOOP'
DROP TABLE DummyTrans
SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName
END
PRINT 'FIM 1º LOOP'
SET @StmtSQL = N'SELECT ''Final Size of LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'' AS ' + @DB
Exec sp_ExecuteSQL @StmtSQL
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
