SQL Script to Shrink all Databases Log Files in Microsoft SQL Server (2000, 2005 and 2008)

I recommend set recovery model to simple before do this (script here). Note, this will purge all database logs.

SQL 2000:

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'
exec sp_msforeachdb @ssql

SQL 2005:

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'
exec sp_msforeachdb @ssql

SQL 2008:

declare @ssql nvarchar(4000)
set @ssql= '
if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
use [?]
declare @tsql nvarchar(4000) set @tsql = ''''
declare @iLogFile int
declare @sLogFileName varchar(55)
declare @RecoveryModel nvarchar(10)
declare LogFiles cursor for
select fileid from sysfiles where status & 0x40 = 0x40
open LogFiles
fetch next from LogFiles into @iLogFile
while @@fetch_status = 0
begin
set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 10) ''
fetch next from LogFiles into @iLogFile
end
select @RecoveryModel = CONVERT(nvarchar(10),Databasepropertyex(name, ''Recovery'')) from master..sysdatabases where name = ''?''
set @tsql = ''USE [?]; '' + @tsql + '' ALTER DATABASE [?] SET RECOVERY SIMPLE '' + @tsql
set @tsql = @tsql + ''; ALTER DATABASE [?] SET RECOVERY '' + @RecoveryModel + '';''
--print @tsql --for debugging
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end'
exec sp_msforeachdb @ssql

SQL Script para diminuir (Shrink) todos os arquivos de log (de todos os bancos de dados) de um servidor Microsoft SQL Server (2000, 2005 and 2008)

Eu recomendo mudar o modelo de recuperação para simples (script aqui) antes de executar o script. Observe que este script vai apagar todos os dados dos arquivos de log.

SQL 2000:

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'
exec sp_msforeachdb @ssql

SQL 2005:

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'
exec sp_msforeachdb @ssql

SQL 2008:

declare @ssql nvarchar(4000)
set @ssql= '
if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
use [?]
declare @tsql nvarchar(4000) set @tsql = ''''
declare @iLogFile int
declare @sLogFileName varchar(55)
declare @RecoveryModel nvarchar(10)
declare LogFiles cursor for
select fileid from sysfiles where status & 0x40 = 0x40
open LogFiles
fetch next from LogFiles into @iLogFile
while @@fetch_status = 0
begin
set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 10) ''
fetch next from LogFiles into @iLogFile
end
select @RecoveryModel = CONVERT(nvarchar(10),Databasepropertyex(name, ''Recovery'')) from master..sysdatabases where name = ''?''
set @tsql = ''USE [?]; '' + @tsql + '' ALTER DATABASE [?] SET RECOVERY SIMPLE '' + @tsql
set @tsql = @tsql + ''; ALTER DATABASE [?] SET RECOVERY '' + @RecoveryModel + '';''
--print @tsql --for debugging
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end'
exec sp_msforeachdb @ssql