DBCC ShrinkFile

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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