Шринк базы sql что это
Как сжать/снизить размеры базы данных в MS SQL
Метод 1: Использование SQLServerManagementStudio
Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)
Шаг 2: Нажимаем на «ОК»
Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).
Метод 2: Использование Transact SQL Command
Следуем за шагами ниже, чтобы уменьшить размер базы данных SQL.
Шаг 1: Открываем наш SQL Server Management Studio
Шаг 2: Подключаемся к необходимой Базе данных
Шаг 3: Нажимаем на «Создать запрос» (NewQuery)
Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)
Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.
Метод 3: Сжатие на уровне строк
Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:
Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.
Осуществим сжатие по строкам.
Результат: занимаемое пространство данными уменьшилось до 5.1 МВ.
Метод 4: Сжатие на уровне страниц
Алгоритмы действия данного сжатия заключается в том, что система проходит по всей таблице, если видит повторяющиеся значения, то вместо копирования этих данных, система создает ссылки на них. Аналогично осуществляется с общими префиксами.
Пример: используем ту же самую таблицу на 14 500 строк.
Осуществим сжатие по страницам.
Результат: занимаемое пространство данными уменьшилось до 2МВ.
Различия между сжатием на уровне страниц и строк
Если кратко резюмировать выше описанные способы, то главное различие между 3 и 4 способом – это данные которые используются в самой базе данных. Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.
Негативные факторы при использовании сжатия
Вывод
Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).
Сжатие файла
В этом подразделе описывается сжатие данных или файла журнала в SQL Server при помощи среды SQL Server Management Studio или Transact-SQL.
Сжатие файлов данных позволяет освободить неиспользуемое пространство путем перемещения страниц данных с конца файла в незанятое пространство ближе к началу файла. Когда в конце файла образуется достаточно свободного места, страницы данных в конце файла могут быть освобождены и возвращены в файловую систему.
В этом разделе
Перед началом работы
Сжатие файла данных или журнала с помощью различных средств.
Перед началом
Ограничения
Рекомендации
безопасность
Permissions
Использование среды SQL Server Management Studio
Сжатие файла данных или журнала
В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engine и разверните его.
Разверните узел Базы данных и щелкните правой кнопкой мыши базу данных, которую нужно сжать.
Укажите пункты Задачи и Сжать, затем выберите пункт Файлы.
База данных
Отображает имя выбранной базы данных.
Имя файла
Выберите файл из списка имеющихся файлов выбранной файловой группы и типа.
Расположение
Отображает полный путь к текущему выбранному файлу. Путь нельзя редактировать, но можно скопировать в буфер обмена.
Выделенное в данный момент место
Для файлов данных отображает выделенное в данный момент место. Для файлов журнала отображается выделенное в данный момент пространство, вычисленное на основании результата процедуры SQLPERF(LOGSPACE) модуля DBCC.
Доступное свободное место
Для файлов данных отображается имеющееся в данный момент доступное свободное место, вычисленное на основании результата процедуры SHOWFILESTATS(идентификатор_файла) модуля DBCC. Для файлов журнала отображается имеющиеся в данный момент доступное свободное место, вычисленное на основании результата процедуры SQLPERF(LOGSPACE) модуля DBCC.
Освободить неиспользуемое место
Все неиспользуемое пространство, выделенное для файлов, освобождается для нужд операционной системы, а файл сжимается в последний выделенный экстент, тем самым размер файла уменьшается без перемещения данных. Не производится попыток перемещения строк на нераспределенные страницы.
Сжать файл до
Определяет размер целевого файла для операции сжатия. Размер не должен быть меньше текущего выделенного пространства или больше общего количества экстентов, выделенных файлу. Если вводимое значение выходит за допустимые границы, оно будет преобразовано к минимальному или максимальному значению при изменении фокуса ввода или при нажатии на любую кнопку панели инструментов.
Очистить файл путем переноса данных в другие файлы той же файловой группы
Выполняется перенос всех данных из указанного файла. Этот параметр позволяет удалить файл при помощи инструкции ALTER DATABASE. Эта возможность эквивалентна выполнению процедуры SHRINKFILE модуля DBCC с параметром EMPTYFILE.
Выберите тип файла и имя файла.
Выбор этого параметра приводит к освобождению всего неиспользуемого пространства файла для ОС и уменьшению размера файла до последнего размещенного экстента. Это уменьшает размер файла без перемещения каких-либо данных.
Выбор этого параметра приводит к освобождению всего неиспользуемого пространства файла для ОС и попытке перемещения строк в неразмещенные страницы.
Выбор этого режима перемещает все данные из указанного файла в другие файлы данной файловой группы. Пустой файл удалить нельзя. Этот режим эквивалентен выполнению процедуры DBCC SHRINKFILE с параметром EMPTYFILE.
Нажмите кнопку ОК.
Использование Transact-SQL
Сжатие файла данных или журнала
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Задача «Сжатие базы данных» (план обслуживания)
Диалоговое окно Задача «Сжатие базы данных» используется для создания задачи, которая пытается уменьшить размер выбранных баз данных. Перечисленные ниже параметры используются для определения количества неиспользуемого пространства, которое должно остаться в базе данных после сжатия (чем больше процент, тем меньше сжимается база данных). Это значение определяется долей фактических данных в базе данных. Например: 100-мегабайтная база данных, содержащая 60 МБ данных и 40 МБ свободного пространства с заданным значением свободного пространства, равным 50 процентам, будет содержать 60 МБ данных и 30 МБ свободного пространства (поскольку 50 процентов от 60 МБ равно 30 МБ). Удаляется только лишнее пространство в базе данных. Допустимые значения: от 0 до 100.
Сжатие файлов данных позволяет освободить неиспользуемое пространство путем перемещения страниц данных с конца файла в незанятое пространство ближе к началу файла. Когда в конце файла образуется достаточно свободного места, страницы данных в конце файла могут быть освобождены и возвращены в файловую систему.
Данные, перемещаемые в процессе сжатия файла, могут быть разбросаны по любым доступным местам в файле. Это вызывает фрагментацию индекса и может увеличить время выполнения запросов, выполняющих поиск в диапазоне индекса. Чтобы устранить фрагментацию, предусмотрите возможность перестроения индексов файла после сжатия.
Эта задача выполняет инструкцию DBCC SHRINKDATABASE.
Параметры
Соединение
Выберите соединение с сервером, которое будет использоваться для выполнения этой задачи.
Создать
Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно Создание соединения описано ниже.
Базы данных
Укажите базы данных, для которых должна выполняться эта задача.
Все базы данных
Позволяет сформировать план обслуживания, который запускает задачи обслуживания для всех баз данных Microsoft SQL Server, кроме tempdb.
Все системные базы данных
Все пользовательские базы данных
Создается план обслуживания, по которому задачи обслуживания выполняются для всех баз данных, созданных пользователем. Для системных баз данных SQL Server задачи обслуживания выполняться не будут.
Следующие базы данных
Создается план обслуживания, по которому задачи обслуживания должны выполняться только для указанных баз данных. Если выбран этот параметр, необходимо выбрать в списке хотя бы одну базу данных.
Планы обслуживания выполняются только для баз данных, уровень совместимости которых 80 или выше. Базы данных с уровнем совместимости 70 или ниже не отображаются.
Сжимать базу данных при превышении ею размера
Укажите размер в мегабайтах, по достижении которого будет выполняться задача.
Объем свободного места после сжатия
Прекратить сжатие по достижении заданного размера свободного пространства в базе данных.
Если количество затронутых объектов велико, построение этого отображения может занять значительное время.
Диалоговое окно «Создание соединения»
Имя соединения
Введите имя нового соединения.
Выберите или введите имя сервера
Выберите сервер для подключения при выполнении этой задачи.
Обновить
Обновите список доступных серверов.
Введите данные для входа на сервер
Укажите способ проверки подлинности на сервере.
Использовать встроенную систему безопасности Windows NT
Подключиться к экземпляру компонента SQL Server Компонент Database Engine c проверкой подлинности Microsoft Windows.
Использовать указанные имя пользователя и пароль
Подключиться к экземпляру компонента SQL Server Компонент Database Engine с использованием проверки подлинности SQL Server. Этот параметр недоступен.
Пароль
Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.
Сжатие базы данных и журнала транзакций в Microsoft SQL Server
Многие администраторы Microsoft SQL Server сталкивались с проблемой значительного увеличения физического размера базы данных и файлов журнала транзакций и, конечно же, им хотелось бы каким-то образом уменьшить этот размер, для того чтобы не предпринимать какие-либо действия, связанные с увеличением свободного пространства на жестком диске. Способ уменьшить физический размер базы данных и файлов журнала транзакций в SQL сервере есть – это сжатие.
Что такое сжатие в Microsoft SQL Server?
Сжатие — это процесс удаления неиспользуемого пространства в файлах базы данных и журнала транзакций.
Физический размер файлов базы данных со временем растет, это связанно с добавлением данных, но при их удалении физический размер файлов остается неизменным, однако в данных файлах появляется логическое неиспользуемое пространство, которое и можно удалить.
Наибольший эффект от сжатия достигается тогда, когда операция сжатия выполняется после операции удаления таблиц из БД или удаления данных из таблиц.
Следует отличать процедуру сжатия журнала транзакций от процедуры усечения журнала транзакций. Сжатие — это уменьшение физического размера журнала за счет удаления неиспользуемого пространства, а усечение – это освобождение места в логическом журнале для повторного использования (т.е. образуется неиспользуемое пространство) журналом транзакций при этом размер физического файла не уменьшается.
Усечение журнала транзакций происходит автоматически:
Если Вы используете модель полного восстановления или в модель восстановления с неполным протоколированием и у Вас файлы журнала транзакций слишком велики, то скорей всего Вы достаточно долго не делали BACKUP (резервную копию) журнала транзакций. В данном случае Вам необходимо сделать сначала BACKUP журнала транзакций, а затем выполнить сжатие журнала транзакций, которое мы как раз и рассмотрим чуть ниже.
Также возможно размер файлов журнала транзакций слишком большой (как при простой, так и при полной модели восстановления) за счет задержки процедуры усечения, т.е. размер журнала, состоит в основном из активной части журнала, а активную часть усечь нельзя, поэтому физический размер журнала растет. На задержку процедуры усечения влияют такие факторы как: активные длительные транзакции, некоторые сценарии отображения зеркальных баз данных и журнала транзакций, некоторые сценарии при репликации транзакций и журнала транзакций, а также усечение журнала невозможно во время операций резервного копирования и восстановления данных. В данном случае Вам нужно устранить причины задержки, затем сделать усечение (т.е. например, для полной модели восстановления BACKUP журнала), а затем сжатие до приемлемых размеров.
Обычно если на постоянной основе с определенной периодичностью создаются резервные копии журнала транзакций или базы данных (при простой модели восстановления), файлы журнала транзакций не растут, и не возникает переполнение журнала транзакций.
Как сжать базу данных в MS SQL Server?
Сжать файлы базы данных и журнала транзакций можно и с помощью графического интерфейса Management Studio и с помощью инструкций Transact-SQL: DBCC SHRINKDATABASE и DBCC SHRINKFILE. Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON.
Примечание! Сжатие базы данных я буду рассматривать на примере Microsoft SQL Server 2016 Express.
Сжимаем базу данных с помощью среды Management Studio
В итоге у Вас откроется окно «Сжатие базы данных», в котором Вы, кстати, можете наблюдать размер базы данных, а также доступное свободное место, которое можно удалить (т.е. сжать). Нажимаем «ОК».
Через некоторое время, в зависимости от размера базы данных, сжатие будет завершено.
Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE
В MS SQL Server для выполнения сжатия файлов базы данных и журнала транзакций существуют две инструкции SHRINKDATABASE и SHRINKFILE.
Для того чтобы выполнить сжатие БД (например, TestBase) точно также как мы это сделали чуть ранее в Management Studio, выполните следующую инструкцию.
SHRINKDATABASE имеет следующие параметры:
Синтаксис SHRINKDATABASE
Для того чтобы сжать только журнал транзакций можно использовать инструкцию SHRINKFILE, например.
В данном случае мы осуществим сжатие файла журнала (TestBase_log – это название файла журнала транзакций), до его начального значения, т.е. до значения по умолчанию. Для того чтобы сжать файл до определенного размера, укажите вторым параметром размер в мегабайтах. Например, следующей инструкцией мы уменьшим размер файла журнала транзакций до 5 мегабайт.
Также необходимо учесть, что если Вы укажете размер меньше того, чем требуется для хранения данных в файле, то файл до этого размера сжат не будет. Например, допустим, если Вы указали 5 мегабайт, а для хранения данных в файле требуется 7 мегабайт, файл будет сжат только до 7 мегабайт.
SHRINKFILE также имеет параметры NOTRUNCATE и TRUNCATEONLY.
Синтаксис SHRINKFILE
Рекомендации и важные моменты при сжатии базы данных
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
На этом у меня все, надеюсь, статья была Вам полезна, удачи!
Параметры автозавода и автосхройки в SQL Server
Оригинальная версия продукта: SQL Server
Исходный номер КБ: 315512
Сводка
Параметры автозаполнения и автосхройки по умолчанию подходят для многих SQL Server систем. Однако существуют среды, в которых вам может потребоваться настроить параметры автозавода и автосхройки. В этой статье приводится ряд справочной информации, которая поможет вам выбрать эти параметры для среды.
Дополнительные сведения
Вот некоторые вещи, которые следует учитывать, если вы решите настроить параметры автозавода и автосхройки.
Настройка параметров
Вы можете настроить или изменить параметры autogrow и autoshrink с помощью одного из следующих следующих параметров:
Заявление ALTER DATABASE
Дополнительные сведения о том, как установить эти параметры на уровне файлов базы данных, просмотрите добавление данных или файлов журнала в базу данных.
Вы также можете настроить параметр autogrow при создании базы данных.
Чтобы просмотреть текущие параметры, запустите следующую команду Transact-SQL:
Имейте в виду, что параметры автогроука находятся в файле. Поэтому необходимо установить их как минимум в двух местах для каждой базы данных (одно для основного файла данных и одно для основного файла журнала). Если у вас несколько файлов данных и/или журналов, необходимо настроить параметры для каждого файла. В зависимости от среды для каждого файла базы данных могут быть разные параметры.
Соображения для AUTO_SHRINK
AUTO_SHRINK — это параметр базы данных в SQL Server. Если включить этот параметр для базы данных, эта база данных будет иметь право на сокращение по фоновой задаче. Эта фоновая задача оценивает все базы данных, удовлетворяющие критериям сжатия и сжатия данных или файлов журналов.
Необходимо тщательно оценить параметр параметра для баз данных в SQL Server экземпляре. Частые операции роста и сокращения могут привести к различным проблемам с производительностью.
Если несколько баз данных проходят частые операции сжатия и роста, это легко приведет к фрагментации уровня файловой системы. Это может серьезно повлиять на производительность. Это верно независимо от того, используете ли вы автоматические параметры или вы вручную растете и сжимаете файлы часто
После AUTO_SHRINK успешного сжатия файлов данных или журналов последующая операция DML или DDL может значительно замедлиться, если потребуется пространство и файлы должны расти.
Фоновая AUTO_SHRINK может занять ресурсы, если существует множество баз данных, которые требуют сокращения.
Фоновая AUTO_SHRINK для получения блокировки и другой синхронизации, которые могут конфликтовать с другими регулярными действиями приложения.
Рассмотрите возможность настройки баз данных до необходимого размера и их предварительного роста. Оставьте неиспользованное пространство в файлах баз данных, если вы считаете, что шаблоны использования приложений будут нуждаться в них снова. Это может предотвратить частый сжатие и рост файлов базы данных.
Соображения для AUTOGROW
Если вы выполните транзакцию, для которой требуется больше пространства журнала, чем доступно, и вы включили параметр autogrow для журнала транзакций этой базы данных, то время завершения транзакции будет включать время, необходимое журналу транзакций для роста на настроенную сумму. Если увеличение роста является большим или существует другой фактор, который заставляет его занять длительное время, запрос, в котором вы открываете транзакцию, может привести к сбой из-за ошибки расплаты. Такая же проблема может быть в результате автоматического вскрытия части данных базы данных.
При запуске крупной транзакции, требуемой для роста журнала, другим транзакциям, которые требуют записи в журнал транзакций, также потребуется дождаться завершения операции роста.
Если в файлах журнала имеется большое количество файлов, может быть слишком большое количество виртуальных файлов журнала (VLF). Это может привести к проблемам с производительностью при запуске базы данных и операциях в Интернете, репликации, зеркальном зеркальном копировании и изменении захвата данных (CDC). Кроме того, иногда это может вызывать проблемы с производительностью при изменении данных.
Если объединить параметры autogrow и autoshrink, можно создать ненужные накладные расходы. Убедитесь, что пороговые значения, которые вызывают операции роста и сжатия, не вызывают частых изменений размера вверх и вниз. Например, можно выполнить транзакцию, из-за которую к моменту совершения журнал транзакций вырастет на 100 МБ. Через некоторое время автосхирк запускает и сжимает журнал транзакций на 100 МБ. Затем вы запустите ту же транзакцию, и журнал транзакций снова вырастет на 100 МБ. В этом примере создается ненужная накладная часть и потенциально создается фрагментация файла журнала, любой из которых может отрицательно повлиять на производительность.
Если вы растёте базу данных небольшими приращениями или вырастёте, а затем сжимаете ее, вы можете получить фрагментацию диска. Фрагментация диска может вызвать проблемы с производительностью в некоторых обстоятельствах. Сценарий небольших приращений к росту также может снизить производительность в вашей системе.
В SQL Server вы можете включить инициализацию мгновенных файлов. Инициализация мгновенных файлов ускоряет выделение файлов только для файлов данных. Инициализация мгновенных файлов не применяется к файлам журнала. Дополнительные сведения см. в инициализации мгновенных файлов базы данных.
Лучшие практики для autogrow и autoshrink
Для управляемой производственной системы необходимо рассматривать автопроизводие как просто непредвиденные обстоятельства для неожиданного роста. Не управлять данными и журналом роста на ежедневной основе с помощью autogrow.
Вы можете использовать оповещения или программы мониторинга для мониторинга размеров файлов и активного роста файлов. Это позволяет избежать фрагментации и переносить эти действия по обслуживанию в не пиковые часы.
Автоматическая сжатие и автозапевка должны быть тщательно оценены обученным администратором баз данных (DBA); Их нельзя оставить безуластия.
Приращение автозавода должно быть достаточно большим, чтобы избежать штрафов за производительность, перечисленных в предыдущем разделе. Точное значение, используемого в параметре конфигурации, и выбор между ростом процента и конкретным ростом размера МБ зависит от многих факторов в вашей среде. Общее правило, используемого для тестирования, заключается в том, чтобы установить параметр autogrow примерно на один-восемь размеров файла.
Включи параметр для каждого файла, чтобы предотвратить рост одного файла до точки, в которой он использует все доступное пространство диска.
Сохраняйте размер транзакций как можно меньше, чтобы предотвратить незапланированный рост файлов.
Почему мне нужно беспокоиться о пространстве диска, если параметры размера управляются автоматически
Параметр autogrow не может увеличивать размер базы данных за пределы доступного дискового пространства на дисках, для которых определены файлы. Поэтому, если вы полагаетесь на функциональность autogrow для размера баз данных, вы все равно должны самостоятельно проверить доступное пространство жесткого диска. Параметр autogrow также ограничен параметром MAXSIZE, выбранным для каждого файла. Чтобы уменьшить вероятность на исходе пространства, можно отслеживать счетчик performance Monitor SQL Server: Объект базы данных: размер файла данных (S) и настроить оповещение о достижении базы данных определенного размера.
Незапланированный рост данных или журнальных файлов может занять место, которое, как ожидается, будет доступно другим приложениям, и может привести к проблемам с другими приложениями.
Увеличение прироста журнала транзакций должно быть достаточно большим, чтобы опережать потребности подразделений транзакций. Даже при включенном автоматическом включите, вы можете получить сообщение о том, что журнал транзакций заполнен, если он не может расти достаточно быстро, чтобы удовлетворить потребности вашего запроса.
SQL Server не постоянно тестировать базы данных, которые попали в установленный порог для autoshrink. Вместо этого он смотрит на доступные базы данных и находит первую, настроенную на автосхренк. Он проверяет эту базу данных и при необходимости сжимает эту базу данных. Затем он ждет несколько минут, прежде чем проверить следующую базу данных, настроенную для autoshrink. Другими словами, SQL Server не проверяет все базы данных одновременно и сокращает их сразу. Он будет работать через базы данных в круговой моды робин, чтобы пошатнул нагрузку в течение периода времени. Таким образом, в зависимости от того, сколько баз данных в определенном экземпляре SQL Server, настроенном на автосхренк, может потребоваться несколько часов с момента, когда база данных достигает порогового значения до реального сжатия.