Ms sql database shrink: Shrink a database — SQL Server

Как сокращать и сжимать данные в SQL Server

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

Для администраторов баз данных это означает здоровенный список дел, который включает в себя все, от очистки старых, ненужных данных до регулярных обновлений и всего, что между ними. Но входит ли правильный размер в контрольный список управления базой данных? Существует множество отличных тактик, которые можно применять для поддержания гибкости и эффективности баз данных. Правильный выбор размера вашей базы данных и поддержание управляемости и гибкости базы данных SQL Server жизненно важны для эффективности и роста вашего бизнеса.

Правильный размер

Правильный выбор размера базы данных — это процесс планирования, который играет важную роль в предварительном проектировании базы данных. Базы данных глубоки, и они должны быть как можно более узкими за счет разумного дизайна. Например, вы можете перейти с Excel на базу данных MS Access, потому что ваши электронные таблицы настолько глубоки и широки, что за деревьями не видно леса.

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

MS Access также имеет несколько полезных запросов на архивирование, которые соберут все эти старые данные, поместят их в отдельные таблицы и отправят в хранилище, чтобы они не мешали повседневным операциям. В MS Access также есть команда compact and repair, которая, помимо восстановления ваших данных, избавляет вас от накопленного мусора, который снижает вашу общую производительность. Дизайн и стратегия вашей базы данных играют большую роль в эффективности ваших приложений и бизнес-операций в целом.

Миграция на SQL Server

Аналогичным образом, когда вы перешли на MS SQL Server, это произошло потому, что вы хотели хранить больше данных и отделить все от ядра, которое ими управляет. Microsoft Access — отличное приложение, но оно (и другие легкие базы данных) достигает конечной точки, когда его объекты — формы, коммутаторы, запросы, макросы и т. д. — начинают теснить ваши данные и конкурировать за место для хранения. Итак, что может сделать ваша организация, когда вы достигнете этой точки?

Варианты уменьшения размера базы данных SQL Server

Почему вам нужно уменьшить размер базы данных SQL Server? Краткий ответ таков: с годами ваша база данных начала нести мертвый груз пустых страниц данных и неиспользуемых или архаичных данных.

Как и Microsoft Access, SQL Server имеет несколько способов шунтировать устаревшие данные и архивировать их. Например, запрос Make-Table найдет старые записи и создаст новую таблицу. Запрос на удаление, в свою очередь, очистит исходную таблицу, удалив архивные записи.

У вас есть два дополнительных способа уменьшить размер базы данных SQL Server: сжать базу данных или сжать ее.

Сжатие базы данных SQL Server

Сжатие файлов данных SQL восстанавливает место примерно так же, как выполняется дефрагментация диска. Он перемещает неиспользуемые 8-килобайтные страницы данных из конца файла в пустое место ближе к началу файла. Идея состоит в том, чтобы освободить достаточно места в конце файла, чтобы его можно было «освободить» и вернуть в систему.

Не попадитесь в «ловушку» уменьшения.

Вот что один программист SQL Пол Рэндал (Paul Randal) сказал в откровенной записи блога «Почему не следует сжимать файлы данных»:

«Сжатие файлов данных никогда не должно быть частью регулярного НИКОГДА, НИКОГДА не включайте автоматическое сжатие… Не попадайтесь в ловушку плана обслуживания, который перестраивает все индексы, а затем пытается высвободить пространство, необходимое для перестроения индексов, запустив сжатие…»

Рэндал называет запуск автоматического сжатия «игрой с нулевой суммой» без фактического увеличения производительности. Microsoft также советует никогда не устанавливать для параметра базы данных AUTO_SHRINK значение ON, если у вас нет «особых требований».

Уменьшите размер базы данных SQL Server, если:

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

Сокращение базы данных имеет ограничения и ограничения. :

Сокращение базы данных:

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

Если он не сломан, не чините его.

Основной вывод о многократном сжатии базы данных SQL Server заключается в том, что большинству баз данных требуется свободное пространство, чтобы быть готовыми к повседневным операциям. Совет Microsoft Technet выглядит следующим образом:

«Если вы многократно сжимаете базу данных и замечаете, что размер базы данных снова увеличивается, это указывает на то, что сжатое пространство требуется для обычных операций. В этих случаях многократное сжатие базы данных является напрасная операция».

Сжатие базы данных SQL Server

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

Недостатком является то, что вам потребуются дополнительные ресурсы ЦП на сервере базы данных для сжатия и распаковки данных по мере их вызова. Сжатие не влияет на некоторые типы данных, например текст и дату. Однако другие типы данных могут быть значительно сжаты. См. диаграмму в MSDN «Реализация сжатия строк» ​​для типов данных SQL Server 2016.

Аналогично, не все таблицы и индексы базы данных SQL Server могут быть сжаты, и не все выпуски SQL Server поддерживают сжатие данных. Активация сжатия данных также лучше всего работает с мастером сжатия данных Microsoft Server Management Studio. Эта статья на sqlmag.com содержит иллюстрированное пошаговое руководство по применению сжатия данных к базе данных SQL Server.

Независимо от того, используете ли вы MS Access или перешли на мощную базу данных SQL Server, ваш план обслуживания базы данных должен включать в себя киберэквивалент контроля веса. Если ваша база данных становится тяжелой, наши дружелюбные специалисты знают, как провести капитальный ремонт или обновление. Мы сделали тысячи из них! Дай нам знать что тебе нужно. Мы можем либо поставить ваши ценные бизнес-данные на диету, либо помочь вам их увеличить.

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

Примечание редактора: этот пост был первоначально опубликован в марте 2016 года и был обновлен для обеспечения свежести, точности и полноты.

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

Клиенту нужен совет, как уменьшить файл журнала транзакций базы данных SQL (*.ldf), поскольку он становится очень большим (например, 10 ГБ+)

Если файл журнала транзакций не может увеличиваться (другими словами, журнал транзакций файл заполнен или жесткий диск, на котором он расположен, заполнен), то пользователь может получать сообщения об ошибках. Примеры см. в отдельных технических заметках 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), поэтому клиентам рекомендуется обращаться в свой ИТ-отдел. эксперт отдела 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-сервера почти заполнен и вам крайне необходимо освободить место
    • Недавно вы запускали процесс (например, описанный в Технической заметке №1624409) который удалил много информации из базы данных, и вы хотите восстановить это «пустое» пространство.

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