Shrink Log Files

DECLARE @script nvarchar(4000)
SET @script = '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'')
BEGIN
    USE [?]
    DECLARE @tsql nvarchar(4000) SET @tsql = ''''
    DECLARE @logFile int
    DECLARE LogFiles CURSOR FOR
        SELECT fileid FROM sysfiles WHERE status & 0x40 = 0x40
    OPEN LogFiles
    FETCH next FROM LogFiles INTO @logFile
    WHILE @@fetch_status = 0
    BEGIN
        SET @tsql = @tsql
            + ''DBCC SHRINKFILE(''
            + cast(@logFile AS varchar(5))
            + '', 1)'' + CHAR(13) + CHAR(10)
        FETCH next FROM LogFiles INTO @logFile
    END
    CLOSE LogFiles
    DEALLOCATE LogFiles
    SET @tsql = @tsql
        + ''BACKUP LOG [?] WITH TRUNCATE_ONLY''
        + CHAR(13) + CHAR(10) + @tsql
    EXEC(@tsql)
END'
EXEC sp_msforeachdb @script

No hay comentarios:

Publicar un comentario