DBCC ShrinkFile

SET NOCOUNT ON
DECLARE @shrinkUnitSize INT, @TotalSizeMB INT, @filename varchar(50), @start_time datetime, @idx int
SET @shrinkUnitSize = 500
SELECT @TotalSizeMB = size/128, @filename= name FROM master..sysaltfiles where dbid=db_id('dba') and fileid =1

SELECT @TotalSizeMB AS CurrentSizeMB, GETDATE() AS StartTime

SET @idx = 1
WHILE (@TotalSizeMB > 300000)-- 현재사이즈가GB보다클때줄이는작업(file사이즈를GB아래로조정하는작업)
BEGIN 
         SET @start_time = GETDATE()
         SET @TotalSizeMB = @TotalSizeMB - @shrinkUnitSize        
         DBCC SHRINKFILE (@filename, @TotalSizeMB) WITH NO_INFOMSGS 

--         SELECT @idx as idx, @TotalSizeMB as TotalSizeMB, DATEDIFF(s, @start_time, getdate()) as duration
         
         PRINT 'idx = ' + CAST(@idx AS varchar(10)) + ', totalSizeMB = ' + CAST(@TotalSizeMB AS varchar(10)) + ', duration = ' + CAST(DATEDIFF(s, @start_time, getdate()) AS varchar(10))

              if DATEDIFF(s, @start_time, getdate() ) < 10 -- 줄어드는시간이10초이하로떨어지면5GB단위로줄임
                    SET @shrinkUnitSize = 5000                          
              
         SET @idx = @idx + 1
END

SELECT @TotalSizeMB AS ShrinkSizeMB, GETDATE() AS EndTime
GO