Como reduzo todos os arquivos rapidamente para todos os bancos de dados?

Quando você faz "Tasks -> Shrink" na GUI, ele realmente emite um DBCC SHRINKDATABASEcomando nos bastidores. Tente. Quando a caixa de diálogo aparecer, não clique no botão "OK". Em vez disso, clique no botão "Script". Você verá o comando em uma janela de consulta. Combine isso com uma consulta em sys.databases (deixe de fora master e msdb), e você pode fazer um script para reduzir todos os bancos de dados.

Por exemplo (retirado do comentário de jcolebrand):

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4;

Copie a saída dessa consulta e execute-a para reduzir todos os seus arquivos.

Que tal uma única linha de instrução sql?

Por favor, leia esta postagem de blog muito interessante antes de executar a seguinte instrução sql.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

DBCC SHRINKDB (e seu primo SHRINKFILE) são extremamente lentos, porque há muita execução de thread único acontecendo nesse código.

Uma maneira muito mais rápida de reduzir um arquivo de banco de dados é esta:

• Alocar um novo grupo de arquivos ao banco de dados
• Torne este grupo de arquivos tão grande quanto necessário (use sp_spaceusedpara determinar o tamanho)
• Reconstrua todos os índices para este novo grupo de arquivos
• Solte o grupo de arquivos antigo

Como as reconstruções de índice são maciçamente paralelas, essa técnica geralmente resulta em uma redução muito mais rápida do banco de dados. Claro, isso requer que você tenha um pouco de espaço extra para o novo grupo de arquivos enquanto o processo está em andamento. No entanto, você só precisa de espaço suficiente no novo grupo de arquivos para manter o maior grupo de arquivos na instância (já que você estará recuperando espaço à medida que avança).

Essa técnica também tem o benefício adicional de desfragmentar seus índices no processo.

Aumentei um pouco a query para encolher apenas o LOG conforme solicitado:

set nocount on  
SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

O código abaixo, obtenha uma lista de bancos de dados que não são do sistema, defina o banco de dados como somente leitura e reduza o arquivo. Eu mantive esse código em algumas caixas do SQL Server usando o SQL Agent Job, onde o espaço é sempre um problema. Na noite de sábado/domingo todas as semanas, ele começa a ser executado e reduz todos os bancos de dados em poucas horas (dependendo do tamanho dos bancos de dados).

declare @db varchar(255)
declare c cursor for
select name from sys.databases where is_read_only=0 and state=0
  and name not in ('master','model','tempdb','msdb')
open c
fetch c into @db
while @@fetch_status=0
begin
  exec SP_dboption @db,'trunc. log on chkpt.','true' 
  DBCC shrinkdatabase (@db)
  fetch next from c into @db
end
close c
deallocate c

Podemos repetir SHRINKDBe SHRINKFILEpara todos os bancos de dados dinamicamente:

while @DBID<=@MaxDBID
begin
  -- Used Dynamic SQL for all databases.
  Set @SQL ='Use '+@DBName+ ' '+Char(10)
  Set @SQL += 'DBCC SHRINKFILE('+@Filename+',5)' +Char(10)
  Set @SQL += 'DBCC SHRINKDATABASE('+@DBName+')'+Char(10)

  --#6 Increment DBid for looping over all databases
  Set @DBID = @DBID+1
  Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @DBID and type_Desc = 'LOG'
  Print (@SQL)
  Exec (@SQL)
end

Você pode encontrar detalhes neste artigo .

Reduza todos os arquivos de log, exceto master, model, msdb:

EXEC sp_MSforeachdb '
DECLARE @sqlcommand nvarchar (500)
IF ''?'' NOT IN (''master'', ''model'', ''msdb'')
BEGIN
USE [?]
SELECT @sqlcommand = ''DBCC SHRINKFILE (N'''''' + 
name
FROM [sys].[database_files]
WHERE type_desc = ''LOG''
SELECT @sqlcommand = @sqlcommand + '''''' , 0)''
EXEC sp_executesql @sqlcommand
END'

Este estende a resposta acima, usando um cursor para percorrer as instruções SQL uma a uma. Não é tão curto quanto a resposta de Emrah, mas permite lógica adicional dentro do loop while dentro do cursor.

SELECT 
    'USE [' 
    + databases.name + N']' 
    + CHAR(13) 
    + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' 
    + masterFiles.name 
    + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) 
    + CHAR(10) 
    + CHAR(13) 
    + CHAR(10)                                                                  AS sqlCommand
INTO
    #shrinkCommands
FROM 
    [sys].[master_files] masterFiles 
    INNER JOIN [sys].[databases] databases ON masterFiles.database_id = databases.database_id 
WHERE 
    databases.database_id > 4; -- Exclude system DBs


DECLARE iterationCursor CURSOR

FOR
    SELECT 
        sqlCommand 
    FROM 
        #shrinkCommands

OPEN iterationCursor

DECLARE @sqlStatement varchar(max)

FETCH NEXT FROM iterationCursor INTO @sqlStatement

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC(@sqlStatement)
    FETCH NEXT FROM iterationCursor INTO @sqlStatement
END

-- Clean up
CLOSE iterationCursor
DEALLOCATE iterationCursor
DROP TABLE #shrinkCommands