Ao gerenciar um banco de dados do SQL Server, é importante reduzir periodicamente o banco de dados para recuperar espaço não utilizado e otimizar o desempenho. No entanto, ao usar a interface gráfica do usuário (GUI) para reduzir um banco de dados, você pode perceber que nem todo o espaço livre é liberado. Isso pode ser problemático, especialmente em sistemas de produção OLTP onde a otimização de espaço é crucial.

Neste artigo, discutiremos um script T-SQL que permite reduzir um arquivo de banco de dados em incrementos até atingir um limite de espaço livre desejado. Ao usar este script, você pode garantir que as páginas do banco de dados sejam reorganizadas e todo o espaço não utilizado seja liberado.

Antes de mergulharmos no script, vamos entender as variáveis envolvidas:

• @DBFileName: Defina esta variável com o nome do arquivo de banco de dados que você deseja reduzir.
• @TargetFreeMB: Defina esta variável com o espaço livre desejado no arquivo em MB após a operação de redução.
• @ShrinkIncrementMB: Defina esta variável com o incremento pelo qual você deseja reduzir o arquivo em MB.

Agora, vamos dar uma olhada no script T-SQL:

set nocount on
declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

-- Defina o nome do arquivo de banco de dados a ser reduzido
set @DBFileName = ''

-- Defina o espaço livre desejado no arquivo em MB após a redução
set @TargetFreeMB = 0

-- Defina o incremento para reduzir o arquivo em MB
set @ShrinkIncrementMB = 100

-- Mostra o tamanho, espaço usado, espaço não utilizado e nome de todos os arquivos de banco de dados
select [FileSizeMB] = convert(numeric(10, 2), round(a.size / 128., 2)),
       [UsedSpaceMB] = convert(numeric(10, 2), round(fileproperty(a.name, 'SpaceUsed') / 128., 2)),
       [UnusedSpaceMB] = convert(numeric(10, 2), round((a.size - fileproperty(a.name, 'SpaceUsed')) / 128., 2)),
       [DBFileName] = a.name
from sysfiles a

declare @sql varchar(8000)
declare @SizeMB float
declare @UsedMB float

-- Obter o tamanho atual do arquivo em MB
select @SizeMB = size / 128. from sysfiles where name = @DBFileName

-- Obter o espaço usado atualmente em MB
select @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.0

-- Loop até que o arquivo atinja o tamanho desejado
while @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB
begin
    set @sql = 'dbcc shrinkfile (' + @DBFileName + ', ' + convert(varchar(20), @SizeMB - @ShrinkIncrementMB) + ') WITH NO_INFOMSGS'
    print 'Iniciando ' + @sql + ' em ' + convert(varchar(30), getdate(), 121)
    exec (@sql)
    print 'Concluído ' + @sql + ' em ' + convert(varchar(30), getdate(), 121)

    -- Obter o tamanho atual do arquivo em MB
    select @SizeMB = size / 128. from sysfiles where name = @DBFileName

    -- Obter o espaço usado atualmente em MB
    select @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.0

    print 'SizeMB=' + convert(varchar(20), @SizeMB) + ' UsedMB=' + convert(varchar(20), @UsedMB)
end

select [EndFileSize] = @SizeMB,
       [EndUsedSpace] = @UsedMB,
       [DBFileName] = @DBFileName

Ao executar este script no banco de dados com o arquivo que você deseja reduzir, você pode reduzir gradualmente o tamanho do arquivo até atingir o limite de espaço livre desejado. O script usa o comando dbcc shrinkfile para reduzir o arquivo em incrementos especificados pela variável @ShrinkIncrementMB.

Ao longo do script, você notará que ele recupera o tamanho atual do arquivo e o espaço usado em MB usando a tabela do sistema sysfiles e a função fileproperty. Em seguida, ele compara o tamanho atual do arquivo com o tamanho desejado e continua reduzindo o arquivo até que o limite de espaço livre desejado seja atingido.

A execução deste script fornecerá informações sobre o tamanho do arquivo, espaço usado e espaço não utilizado para todos os arquivos de banco de dados. Ele também exibirá o progresso da operação de redução, permitindo que você monitore o processo.

Ao usar este script T-SQL, você pode otimizar a operação de redução do seu banco de dados do SQL Server e garantir que todo o espaço não utilizado seja recuperado. Isso pode levar a um melhor desempenho e utilização eficiente do espaço em disco.

Lembre-se de ter cuidado ao reduzir um arquivo de banco de dados, especialmente em ambientes de produção. É recomendado testar o script em um ambiente não produtivo antes de aplicá-lo a sistemas críticos.