Sql shrink database что это

Сжатие базы данных и журнала транзакций в 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.

На этом у меня все, надеюсь, статья была Вам полезна, удачи!

Источник

Задача «Сжатие базы данных» (план обслуживания)

Диалоговое окно Задача «Сжатие базы данных» используется для создания задачи, которая пытается уменьшить размер выбранных баз данных. Перечисленные ниже параметры используются для определения количества неиспользуемого пространства, которое должно остаться в базе данных после сжатия (чем больше процент, тем меньше сжимается база данных). Это значение определяется долей фактических данных в базе данных. Например: 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. Этот параметр недоступен.

Пароль
Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.

Источник

DBCC SHRINKDATABASE (Transact-SQL)

Сокращает размер файлов данных и файлов журнала в указанной базе данных.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

имя_бд | ид_бд | 0
Имя или идентификатор базы данных, которая должна быть сжата. Если указано значение 0, используется текущая база данных.

целевой_процент
Процент свободного пространства, которое должно остаться в базе данных после сжатия.

NOTRUNCATE
Перемещает назначенные страницы с конца файла в неназначенные страницы в начале файла. Это действие сжимает данные в файле. Целевой_процент является необязательным. Azure Synapse Analytics не поддерживает этот параметр.

Свободное место в конце файла не возвращается операционной системе, и физический размер файла не изменяется. Таким образом, база данных физически не уменьшается при указании NOTRUNCATE.

Аргумент NOTRUNCATE применим только к файлам данных. NOTRUNCATE не влияет на файл журнала.

TRUNCATEONLY
Освобождает все свободное пространство в конце файла и возвращает его операционной системе. Не перемещает какие-либо страницы в файле. Файл данных сжимается только до последнего назначенного экстента. Аргумент целевой_процент не учитывается, если указан аргумент TRUNCATEONLY. Azure Synapse Analytics не поддерживает этот параметр.

Аргумент TRUNCATEONLY оказывает влияние на файл журнала. Для усечения только файла данных используйте инструкцию DBCC SHRINKFILE.

WITH NO_INFOMSGS
Подавляет все информационные сообщения со степенями серьезности от 0 до 10.

Результирующие наборы

В следующей таблице отображены столбцы результирующего набора.

Компонент Компонент Database Engine не отображает строки для файлов, размер которых не был сокращен.

Remarks

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

Чтобы сжать все файлы данных и журналов указанной базы данных, выполните команду DBCC SHRINKDATABASE. Чтобы сжать один файл данных или файл журнала в указанной базе данных, выполните команду DBCC SHRINKFILE.

Чтобы просмотреть количество свободного (нераспределенного) пространства в базе данных, выполните процедуру sp_spaceused.

Операции DBCC SHRINKDATABASE могут быть остановлены на любом этапе процесса, при этом вся выполненная работа сохраняется.

Размер базы данных нельзя сделать меньше минимального настроенного размера базы данных. Минимальный размер указывается при создании базы данных. Также минимальный размер может быть последним размером, явно установленным в операции изменения размера файла. Такие операции, как DBCC SHRINKFILE или ALTER DATABASE, — примеры операций, изменяющих размер файла.

Предположим, что база данных была создана с размером 10 МБ. Затем она увеличивается до 100 МБ. Наименьший размер базы данных, до которого ее можно сжать, — 10 МБ, даже если все данные в базе данных будут удалены.

При выполнении команды DBCC SHRINKDATABASE укажите параметр NOTRUNCATE или TRUNCATEONLY. Если этого не сделать, результат будет таким же, как если бы вы выполнили операцию DBCC SHRINKDATABASE с аргументом NOTRUNCATE и последующим запуском операции DBCC SHRINKDATABASE с аргументом TRUNCATEONLY.

База данных не обязана находиться в однопользовательском режиме. Другие пользователи могут работать в базе данных (в том числе системной) при ее сжатии.

Невозможно сжать базу данных во время создания ее резервной копии. И наоборот, невозможно создать резервную копию базы данных во время операции сжатия.

Работа команды DBCC SHRINKDATABASE

Инструкция DBCC SHRINKDATABASE сжимает файлы данных по одному, а файлы журнала так, как будто все они представляют один непрерывный пул журнала. Сжатие файлов всегда ведется с конца.

Предположим, имеется несколько файлов журнала, файл данных и база данных с именем mydb. Каждый файл данных и журнала имеет размер 10 МБ, а файл данных содержит 6 МБ данных. Компонент Компонент Database Engine вычисляет целевой размер каждого файла. Это размер, до которого файл должен быть сжат. Если инструкция DBCC SHRINKDATABASE указана с аргументом целевой_процент, то компонент Компонент Database Engine вычисляет целевой размер таким образом, чтобы в файле после сжатия был целевой_процент свободного пространства.

Например, если указать целевой_процент со значением 25 для сжатия базы данных mydb, компонент Компонент Database Engine рассчитает целевой размер для файла, равный 8 МБ (6 МБ данных и 2 МБ свободного пространства). Поэтому компонент Компонент Database Engine перемещает все данные из последних 2 МБ файла данных в любое свободное пространство в первых 8 МБ файла данных, а затем сжимает файл.

Предположим, что файл данных базы данных mydb содержит 7 МБ данных. При задании значения 30 для аргумента целевой_процент можно сжать этот файл данных до 30 %. Но задание значения 40 для целевой_процент не позволит сжать файл данных, так как Компонент Database Engine не может уменьшить файл до размера, меньшего, чем занимают данные сейчас.

Эту ситуацию можно представить и другим способом: 40 процентов желаемого свободного пространства + 70 процентов от полного файла данных (7 МБ из 10 МБ) больше, чем 100 процентов. Любой целевой_процент больше 30 не приведет к сжатию файла данных. Сжатия не будет, поскольку сумма освобождаемого процента и текущего процента, занятого в файле данных, превышает 100 процентов.

Для файлов журнала Компонент Database Engine вычисляет целевой размер всего журнала на основе аргумента целевой_процент. Вот почему целевой_процент — это объем свободного места в журнале после операции сжатия. Целевой размер всего журнала затем пересчитывается в целевой размер каждого файла журнала.

Инструкция DBCC SHRINKDATABASE пытается немедленно сжать каждый физический файл журнала до его целевого размера. Предположим, что в виртуальных файлах журнала нет частей логического журнала за пределами целевого размера файла журнала. Тогда файл будет успешно усечен, и инструкция DBCC SHRINKDATABASE завершится без сообщений. Однако если какая-то часть логического журнала хранится в виртуальных журналах за пределами заданного размера, то компонент Компонент Database Engine освобождает как можно больше места, а затем выдает информационное сообщение. Сообщение описывает действия, которые необходимо предпринять, чтобы переместить логический журнал из виртуальных журналов в конец файла. После выполнения всех действий инструкция DBCC SHRINKDATABASE может быть использована для освобождения оставшегося пространства.

Файл журнала может быть сжат только до границы виртуального файла журнала. Именно поэтому сжатие файла журнала до размера, меньшего, чем размер виртуального файла журнала, может оказаться невозможным. Это также может быть невозможно, даже если он не используется. Размер виртуального файла журнала динамически выбирается компонентом Компонент Database Engine при создании или расширении файлов журнала.

Рекомендации

Обратите внимание на следующие сведения при планировании сжатия базы данных.

Устранение неполадок

Операции сжатия могут быть блокированы транзакцией, запущенной с уровнем изоляции, основанным на управлении версиями строк. Например, если выполняется масштабная операция удаления под уровнем изоляции с управлением версиями строк, когда запускается инструкция DBCC SHRINK DATABASE. Когда это происходит, операция сжатия будет ожидать, пока завершится операция удаления, прежде чем приступить к сжатию файлов. При возникновении такого ожидания для операций DBCC SHRINKFILE и DBCC SHRINKDATABASE выводится информационное сообщение (5202 для SHRINKDATABASE и 5203 для SHRINKFILE). Это сообщение выводится в журнал ошибок SQL Server каждые пять минут в течение первого часа, а затем каждый последующий час. Например, журнал ошибок содержит следующее сообщение об ошибке:

Эта ошибка означает, что транзакции моментальных снимков с отметками времени старше 109 заблокируют операцию сжатия. Такая транзакция — последняя транзакция, завершаемая операцией сжатия. Это также показывает, что столбцы transaction_sequence_num или first_snapshot_sequence_num в динамическом административном представлении sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) содержат значение 15. Столбцы transaction_sequence_num или first_snapshot_sequence_num в представлении содержат меньшее число, чем последняя транзакция, выполненная операцией сжатия (109). В этом случае операция сжатия будет ждать завершения этих транзакций.

Разрешить эту проблему можно одним из следующих способов.

Разрешения

Примеры

A. Сжатие базы данных и определение количества свободного пространства в процентах

В следующем примере уменьшается размер файлов данных и журнала в пользовательской базе данных UserDB с целью освободить 10 процентов свободного пространства в базе данных.

Б. Усечение базы данных

В следующем примере файлы данных и журнала в образце базы данных AdventureWorks сжимаются до последнего выделенного экстента.

Источник

Сжатие файла

В этом подразделе описывается сжатие данных или файла журнала в 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.

На панели «Стандартная» нажмите Создать запрос.

Источник