При управлении базой данных SQL Server важно периодически уменьшать базу данных, чтобы вернуть неиспользуемое пространство и оптимизировать производительность. Однако, при использовании графического интерфейса для уменьшения базы данных, вы можете заметить, что он не освобождает все свободное пространство. Это может быть проблематично, особенно в производственных системах OLTP, где оптимизация пространства является важной.

В этой статье мы рассмотрим скрипт T-SQL, который позволяет уменьшить размер файла базы данных постепенно до достижения заданного предела свободного пространства. Используя этот скрипт, вы можете убедиться, что страницы базы данных переупорядочены и все неиспользуемое пространство освобождено.

Прежде чем мы перейдем к скрипту, давайте разберемся с переменными, которые используются:

• @DBFileName: Установите эту переменную в имя файла базы данных, который вы хотите уменьшить.
• @TargetFreeMB: Установите эту переменную в желаемое свободное пространство файла после операции уменьшения в МБ.
• @ShrinkIncrementMB: Установите эту переменную в инкремент, на который вы хотите уменьшить файл в МБ.

Теперь давайте посмотрим на скрипт T-SQL:

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

-- Установите имя файла базы данных для уменьшения
set @DBFileName = ''

-- Установите желаемое свободное пространство файла после уменьшения
set @TargetFreeMB = 0

-- Установите инкремент для уменьшения файла в МБ
set @ShrinkIncrementMB = 100

-- Показать размер, используемое пространство, неиспользуемое пространство и имя всех файлов базы данных
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

-- Получить текущий размер файла в МБ
select @SizeMB = size / 128. from sysfiles where name = @DBFileName

-- Получить текущее используемое пространство в МБ
select @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.0

-- Цикл до достижения файла желаемого размера
while @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB
begin
    set @sql = 'dbcc shrinkfile (' + @DBFileName + ', ' + convert(varchar(20), @SizeMB - @ShrinkIncrementMB) + ') WITH NO_INFOMSGS'
    print 'Start ' + @sql + ' at ' + convert(varchar(30), getdate(), 121)
    exec (@sql)
    print 'Done ' + @sql + ' at ' + convert(varchar(30), getdate(), 121)

    -- Получить текущий размер файла в МБ
    select @SizeMB = size / 128. from sysfiles where name = @DBFileName

    -- Получить текущее используемое пространство в МБ
    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

Запустив этот скрипт в базе данных с файлом, который вы хотите уменьшить, вы можете постепенно уменьшать размер файла до достижения желаемого предела свободного пространства. Скрипт использует команду dbcc shrinkfile для уменьшения файла с заданным инкрементом, указанным в переменной @ShrinkIncrementMB.

Во время выполнения скрипта вы заметите, что он получает текущий размер файла и используемое пространство в МБ, используя системную таблицу sysfiles и функцию fileproperty. Затем он сравнивает текущий размер файла с желаемым размером и продолжает уменьшать файл, пока не достигнут желаемый предел свободного пространства.

Запуск этого скрипта предоставит вам информацию о размере файла, используемом пространстве и неиспользуемом пространстве для всех файлов базы данных. Он также отобразит прогресс операции уменьшения, позволяя вам отслеживать процесс.

Используя этот скрипт T-SQL, вы можете оптимизировать операцию уменьшения вашей базы данных SQL Server и убедиться, что все неиспользуемое пространство возвращено. Это может привести к улучшению производительности и эффективному использованию дискового пространства.

Помните, что следует быть осторожным при уменьшении файла базы данных, особенно в производственных средах. Рекомендуется протестировать скрипт в непроизводственной среде перед его применением к критическим системам.