Как уменьшить размер mdf файла (MS SQL база данных)

Reduce MDF file size

 Шаг 0) Сокращение ответственности

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

В своих комментариях вы упомянули, что удалили 50% данных в базе, поэтому, скорее всего, у вас есть хороший повод для однократного сжатия базы данных. (При условии, что удалённые данные не вернутся.)

Я даже не буду говорить о сокращении журналов. Мы просто говорим о файлах данных.

Шаг 1) Посмотрите на размер файла

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

SELECT 
     LogicalName = dbf.name
    ,FileType = dbf.type_desc
    ,FilegroupName = fg.name
    ,PhysicalFileLocation = dbf.physical_name
    ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
    ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
           - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0))
    ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 
           - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf 
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id 
ORDER BY dbf.type DESC, dbf.name; 

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

Шаг 2) Рассмотрим побочные эффекты

Если вы уменьшаете размер файла данных, все данные, расположенные в «конце» файла, должны быть связаны с другими данными в файле. Эти операции регистрируются, поэтому вы будете часто использовать журнал транзакций (если только ваша база данных не находится в режиме SIMPLE восстановления). Убедитесь, что журнал транзакций достаточно большой, и что вы достаточно часто создаете резервные копии журнала транзакций.

Если ваша база данных зеркалируется, находится в группе доступности или выполняет доставку журналов, вы можете обнаружить, что ваш вторичный сервер (серверы) отстаёт в работе во время этой операции.

При сжатии будет создаваться много фрагментов. В зависимости от вашей рабочей нагрузки это может повлиять на производительность в процессе сжатия.

При сжатии возникает множество операций ввода-вывода. Если у вас уже есть проблемы с вводом-выводом, они могут усугубиться при сжатии, и это может повлиять на производительность в процессе.

Сокращение — это в основном онлайн-процесс, при котором данные перемещаются по одной странице в начало файла, чтобы можно было обрезать его конец. Я говорю в основном онлайн, потому что эта операция может привести к блокировке — я особенно часто сталкивался с этим в файловой группе PRIMARY. Из-за возможности блокировки я никогда не запускаю эту операцию без присмотра и никогда не делаю этого в часы пик.

Будьте действительно уверены, что вам нужно сжаться.

Шаг 3) Сжать файлы

Я всегда использую DBCC SHRINKFILE(BOL) при сжатии, никогда DBCC SHRINKDATABASE. Если я собираюсь сжать файл, я хочу иметь как можно больше контроля над процессом.

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

Теперь вы можете создать оператор (операторы) для уменьшения размера файла данных. Помните, что это онлайн-процесс, но если вы беспокоитесь о влиянии на производительность, внимательно следите за своим сервером во время выполнения этого процесса:

USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Шаг 4) Просмотрите фрагментацию

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

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

Если у вас нет регулярной работы по обслуживанию индекса, но она вам нужна, выясните, какие есть лучшие варианты и нужно ли вообще это делать. Кендра Литтл хорошо вводит в курс дела здесь. Мне лично нравится процедура IndexOptimize от Олы Халлнгрена.

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

Это можно сделать полностью онлайн. Вы можете использовать DBCC SHRINKFILE и REORGANIZE для этого полностью онлайн. Если сжатие приводит к значительной фрагментации, которая влияет на производительность, вы можете уменьшить/реорганизовать/уменьшить/реорганизовать данные небольшими фрагментами. Это займет очень, очень много времени. Но это компромисс... Есть более быстрые методы, но они, скорее всего, приведут к временному простою.

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

Источник: здесь