frederico_fonseca - Thursday, March 23, 2017 6:32 PM

Something like this.

declare @sql nvarchar(300)
declare @shrink_step int = 20000 -- size in MB to reduce
declare @shrink_target int = 150000 -- adapt as required - but sql below will set it to current space used + 10%
declare @db_file_name sysname = N'test'
declare @current_size int

select @current_size = convert(float, size) * (8192.0 / 1048576)
  , @shrink_target =
    case
    when floor((convert(float, fileproperty(name, 'SpaceUsed')) * (8192.0 / 1048576) + .9) * 1.1) > @shrink_target
     then floor((convert(float, fileproperty(name, 'SpaceUsed')) * (8192.0 / 1048576) + .9) * 1.1)
    else @shrink_target
    end

from sysfiles
where name = @db_file_name

--select @current_size
--  , @shrink_target

while (@current_size - @shrink_step) > @shrink_target
begin
  set @current_size = @current_size - @shrink_step
  set @sql = 'dbcc shrinkfile (N''' + @db_file_name + ''', ' + convert(varchar(20), @current_size) + ') with no_infomsgs'
  print @sql
  exec sp_executesql @sql
end