Truncar logs sql server2003

[3com 0]

alguem sabe como fazer para truncar logs no sql server2003? Meus logs estao mto grandes preciso diminui-los. Help...........

[3com 0]

Se alguem precisar, descubri a resposta, esta é uma rotina que limpa(diminue)o tamanho dos logs do banco, serve pra sql 7 e pra sql 2000...você so tera que colocar o nome do seu banco, onde encontra-se <nome do banco>, você vai colocar o nome do seu banco, e onde tem @NewSize = 200 , você vai colocar o tamanho do seu log atual. e é so colocar no query analyzer e rodar...USE GO/* Reduz Log */SET NOCOUNT ONDECLARE @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 DummyTransPRINT '*** Perform a full database backup ***'SET NOCOUNT OFF

[Fernando_Castro 0]

Ola 3comno SQL Server 2000 eu uso seguinte comandoDUMP TRAN nome_do_banco WITH NO_LOGe logo em seguinda você executa um SHRINK para ele liberar o espaço em disco.DBCC SHRINKDATABASE ( 'nome_do_banco', NOTRUNCATE )Espero ter ajudado.