Shrink database ms sql: Shrink a database — SQL Server

Усечение файлов журналов транзакций MS SQL — Блог

Усечение файлов журналов транзакций MS SQL

10 июня 2016

0

Maxim Zhukov

Постановка проблемы

По разным причинам (например произведенной ранее реиндексацией rebuild index) может случиться, что операция Shrink Database или Shrink file не освобождают дисковое пространство занимаемое файлами журналов транзацкий (transaction log) даже после операций резервного копирования с включением усечение журнала транзакций (truncate log) в полной модели восстановления (recovery model full).

 

Усечение возможно только после достижения контрольной точки. в описанной постановке задачи требовалось сократить всеми средствами и силами занимаемый базами данных размер на файловой системе, с минимизацией рисков потери/порчи данных и простоях системы. 

 

Необходимые действия

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

 — выполнитьрезервную копию журнала транзакций transaction log

 — перевести базу данных в readonly (снизить риски)

 

 Далее необходимо переключить модель восстановления базы данных в Simple (внимание, на этом шаге выполняется критическое изменение).

 После перевода, уже не актуальный файл журналов транзакций можно «зарезать» с помощью DBCC SHRINKFILE, например, до 1 МБ.

  DBCC SHRINKFILE (databasename, 1)

  Далее возвращаем обратно модель восстановления базы данных (Full), переводим базу в Read/Write.  

  

 Комманды для выполнения описанных действий

USE databasename ;  
ALTER DATABASE databasename SET READ_ONLY
BACKUP LOG databasename to DISK = 'Backup storage\databasename.trn';
ALTER DATABASE databasename SET RECOVERY Simple ;
DBCC SHRINKFILE (databasename_log, 1)
ALTER DATABASE databasename SET RECOVERY FULL ;
ALTER DATABASE databasename SET READ_WRITE

Вместо эпилога

  В конце заметки хочу отметить, что необходимость операции Shrink database является спорной [1][2][3][4][5][6][7]…, а предложенный в заметке путь со сменой модели восстановления для некоторых дикостью. Я не агитирую за «Красную Армию», а привожу возможное решение конкретной поставленной задачи сокращения «несокращаемых» maintenance операциями файлов журналов транзакций.

Сокращение файлов данных SQL Server: передовой опыт и причины неудач

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

Традиционно каждый раз, когда вы спрашиваете администратора баз данных, как сделать сжатие менее отстойным, они начинают разглагольствовать о том, что сжатие — это плохо, и вам просто не следует этого делать. Иногда звучит как-то сердито.

Люди пытаются сказать, что сжатие файлов данных обычно происходит медленно, неприятно и болезненно для вас.

Сжатие файлов данных — отстой, и у вас не так много способов сделать его менее отстойным

Вот с чем вы сталкиваетесь, когда дело доходит до сжатия:

  • Сжатие может привести к блокировке во время работы. Вот пост, который я написал некоторое время назад с демонстрационным скриптом для воспроизведения блокировки.
  • Сжатие может перестать работать, и вы не узнаете, почему.
    • В одном случае DBCC SHRINKFILE останавливался из-за того, что он попал в тупик и был объявлен жертвой тупика. Однако это сообщение не отображалось в окне сообщений для сеанса, в котором запущен SHRINKFILE! Веселье? (Не совсем.)
  • Сокращение фрагментов ваших индексов — если индексы большие и вы запускаете для них команду перестроения, вы все равно можете просто заново вырастить файлы данных.
  • Если сжатие должно перемещать данные LOB, это очень медленно. Пол Рэндал объясняет, почему здесь.
    • Обратите внимание, что вы можете нажать это, даже если данные, которые вы удалили , не содержат данных LOB. Если есть LOB-страницы ближе к концу файла, SQL подберет эти страницы и переместит их к началу файла.

Это не твоя вина, что у сжатия есть весь этот багаж. Вы просто хотите иметь меньшую базу данных. Но именно из-за этих вещей обычно стараются избегать сжатия, когда это возможно.

Большинство старших администраторов баз данных заблаговременно сохраняют свободное место в базе данных и регулярно архивируют данные

Планирование сокращается с каждым днем.

Рост и обработка данных никогда не должны зависеть от дискового пространства. Данные важнее места на диске!

  • Регулярно очищайте данные, которые действительно больше не нужны.
    • Если у вас есть ночной процесс, который удаляет старые данные небольшими партиями, а не раз в квартал, это обычно лучше для производительности. Это зависит от того, когда используется ваша база данных и может ли ваш процесс удаления данных заблокировать пользователей.
    • Иногда для бизнеса желательно быстро удалить данные, которые больше не нужны по юридическим причинам. Однако это сильно зависит от отрасли.
  • Регулярно измеряйте рост данных и начинайте оценивать, насколько данные могут увеличиться в будущем
  • Оставьте свободное место в базе данных, чтобы обеспечить рост данных без увеличения файлов.
    • Это означает упреждающий запрос дополнительного места на диске и упреждающий рост файлов. Вы можете делать это ежеквартально или один или два раза в год — как вам удобнее, но настройте напоминания и не пропускайте.

Диаграммы роста объема данных и заблаговременное управление пространством уберегут вас от реактивного «режима сокращения».

Кроме того, вы хорошо выглядите в глазах своего руководства, если вы в курсе всего этого!

Иногда приходится сжимать файлы данных

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

В этих случаях:

  • Рассмотрите обходные пути. Иногда легче импортировать оставшиеся данные в новую базу данных во время простоя, в зависимости от того, сколько вы удаляете и сколько осталось.
    • Это также может позволить вам тщательно спланировать хорошую файловую группу и расположение файлов в новой базе данных.
  • Определите малое время использования, когда вы можете запустить усадку.
  • Используйте DBCC SHRINKFILE и задайте конкретный целевой размер для файла, который вы сжимаете.
  • Следите за своими заданиями резервного копирования и убедитесь, что они выполняются успешно и не занимают больше времени, чем обычно.
  • Запланируйте оставить пустое место в ваших файлах данных, чтобы обеспечить возможность роста в течение следующего года и разрешить перестроение индекса, если это необходимо, после завершения сжатия. Нет необходимости чрезмерно сжиматься, а затем снова расти.
  • Следите за блокировкой, если у вас нет окна простоя
  • Если вам нужно знать, сколько времени займет сжатие, восстановите копию базы данных в другом месте и заранее протестируйте, чтобы получить представление, но это все равно будет приблизительная оценка.
    • Извне очень сложно узнать, сколько данных LOB необходимо переместить. Блокировка также влияет на скорость аппаратного обеспечения и диска, поэтому ее очень сложно оценить.
  • Убедитесь, что вы регулярно запускаете команду DBCC CHECKDB. Потому что ты должен это делать! (Нет, сжатие не должно вызывать порчи, но небольшая осторожность тоже не помешает.)

И, может быть, найти сериал на Netflix, который можно посмотреть, пока работает психотерапевт. Что-то, что требует всего пары клеток мозга, чтобы посмотреть, и что вы не против поставить на паузу, если что-то пойдет не так.

Как *вручную* уменьшить размер журнала транзакций базы данных Microsoft SQL, чтобы освободить место на диске пример 10Gb+)

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

  • Например, в файле журнала пакетного задания можно найти следующее сообщение:

Ошибка: Файл журнала для базы данных ‘dbname’ заполнен. Создайте резервную копию журнала транзакций для базы данных, чтобы освободить место в журнале.

Несмотря на то, что файлы журналов транзакций могут стать умеренно большими, чрезмерно большие файлы журналов транзакций ненормальны.

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

Если файлы журнала относительно велики (например, такого же размера, как файл данных ‘mdf’), то это часто является признаком того, что что-то не так с конфигурацией сервера Microsoft SQL.

  • Другими словами, большой файл журнала транзакций часто является признаком плохо оптимизированного сервера Microsoft SQL.

Например, IBM Cognos рекомендует (из соображений производительности) для сервера MS SQL, на котором размещены базы данных Controller, была запланирована задача автоматической оптимизации для регулярных задач повторной индексации и обновления статистики.

  • Дополнительные сведения см. в отдельной технической заметке IBM № 1396973.

В эту процедуру обслуживания легко запланировать полезные задачи (такие как резервное копирование и усечение журналов).

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

Эти инструкции подходят как для SQL 2005, так и для более поздних версий.

Продукты IBM Cognos (например, Controller) могут хранить свою информацию в различных стандартных отраслевых форматах баз данных, которые продаются сторонними (не IBM) поставщиками (такими как Microsoft и Oracle).

ВАЖНО :

  • IBM Cognos напрямую не предоставляет поддержку каких-либо сторонних продуктов (например, Microsoft SQL Server), поэтому клиентам рекомендуется обращаться в свой ИТ-отдел. эксперт отдела SQL DBA
  • Информация в этой технической заметке предоставляется исключительно как общее неофициальное руководство для объяснения простых понятий.
  • Официальная документация третьей стороны (Microsoft) имеет приоритет над этой статьей
  • Обученный администратор базы данных Microsoft SQL клиента должен быть хорошо знаком со всеми этими предметами и всегда должен нести ответственность за внедрение любых изменений/передовых практик для конкретной среды клиента.

Долгосрочное решение:
Обратитесь к своему ИТ-специалисту. администратору SQL отдела (DBA) для выполнения запланированного задания обслуживания SQL, которое будет автоматическое резервное копирование и усечение журналов транзакций — обычно каждую ночь.

  • Дополнительные сведения см. в отдельной технической заметке IBM № 1396973.

Краткосрочное решение:
Обратитесь к своему ИТ-специалисту. администратору SQL отдела (DBA) на вручную сжать журнал(ы) транзакций для баз данных вашего сервера.

  • Шаги см. ниже.

ЧАСТЬ ПЕРВАЯ : Сокращение журнала транзакций (LDF):
1. Войдите на сервер SQL в качестве администратора
2. Запустите « SQL Server Management Studio » из меню «Пуск»
3. Разверните раздел «Базы данных». ‘ и найдите соответствующую базу данных
4. Щелкните правой кнопкой мыши базу данных и выберите » Задачи — Сжатие — Файлы ‘

5. Измените «Тип файла» на » Журнал »
6. Убедитесь, что параметр » Освободить неиспользуемое пространство «выбрано
7. Нажмите «ОК».

ЧАСТЬ ВТОРАЯ (необязательно): Сжатие файла базы данных (MDF):
.

    ПРИМЕЧАНИЕ. Сокращение файла базы данных « data » ( mdf ) может привести к снижению производительности базы данных.

    • Одной из причин этого является то, что это может привести к фрагментации индексов (дополнительные сведения см. в статье Microsoft TechNet, ссылка на которую приведена ниже).

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

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

1. Щелкните правой кнопкой мыши базу данных и выберите «Задачи — Сжать — Файлы »
2. Убедитесь, что для параметра «Тип файла» установлено значение «9».0112 Данные »
3. Убедитесь, что выбран параметр «Освободить неиспользуемое пространство».
4. Нажмите «ОК». базы данных и выберите «Задачи — Сжать — База данных ‘
2.