Fórum Tamanho do Arquivo .MDF/.LDF #33204

Dica obtida com Rodrigo Fernandes no newsgroup MSDN

Faz um SHRINK no arquivo, procura no books online que tem referências de
como fazer por linha de comando.
DBCC SHRINKFILE ou DBCC SHRINKDATABASE

Ou então faça pelo Enterprise Manager.

Qualquer dúvida retorne.

valeu !!!

/*
Se o comando SHRINK DATABASE não está limpando o seu LOG, utilize o script abaixo.
Você pode utilizar esse script seguidas vezes. Altere o <nome do banco> para o
banco desejado.
Você pode alterar ainda:
*/

USE <nome do banco>
GO
/* Reduz Log */
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT,
@Database_Name varchar(255)
SELECT @Database_Name = ´<nome do banco>´
SELECT name FROM SYSFILES WHERE GROUPID = 0
SELECT @LogicalFileName = name
FROM SYSFILES WHERE GROUPID = 0 -- Use sp_helpfile to identify the logical file name that you want to shrink.
SELECT @MaxMinutes = 1, -- Limit on time allowed to wrap log.
@NewSize = 200 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = Rtrim(@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)
-- 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.
select @LogicalFileName = rtrim(@LogicalFileName)
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
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
PRINT ´*** Perform a full database backup ***´
SET NOCOUNT OFF

Abraços,

Vanius Girodo
Fonte: http://lib.seven.com.br