Сжатие базы данных и журнала транзакций в 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. Этот параметр недоступен.
Пароль
Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.
Сжатие базы данных
В этой статье приводятся инструкции по сжатию базы данных в SQL Server с использованием обозревателя объектов в SQL Server Management Studio или Transact-SQL.
Сжатие файлов данных позволяет освободить неиспользуемое пространство путем перемещения страниц данных с конца файла в незанятое пространство ближе к началу файла. Когда в конце файла образуется достаточно свободного места, страницы данных в конце файла могут быть освобождены и возвращены в файловую систему.
Ограничения
Невозможно сжать базу данных во время создания ее резервной копии. И наоборот, нельзя создать резервную копию базы данных во время операции сжатия.
Рекомендации
Просмотр количества свободного (нераспределенного) пространства в базе данных. Дополнительные сведения см. в разделе Отображение данных и сведений о пространстве журнала для базы данных.
Обратите внимание на следующие сведения при планировании сжатия базы данных.
Наибольший эффект от операции сжатия достигается при ее применении после операции, создающей много неиспользуемого пространства, например после усечения таблицы или удаления таблицы.
Большинству баз данных требуется некоторое свободное пространство для выполнения обычных ежедневных операций. Если сжатие базы данных производится регулярно, но она снова увеличивается в размерах, это означает, что место, освобожденное при сжатии, необходимо для нормальной работы. В таких случаях многократное сжатие базы данных является бесполезным и скорее всего приведет к тому, что освобожденное пространство будет использовано при автоматическом увеличении размера, что отрицательно скажется на производительности.
Операция сжатия не избавляет от фрагментации индексов в базе данных и обычно приводит к еще более сильной фрагментации. Это еще одна причина, по которой не стоит выполнять регулярное сжатие базы данных.
Не следует устанавливать параметр базы данных AUTO_SHRINK в значение ON без достаточных на то оснований.
Permissions
Использование среды SQL Server Management Studio
Сжатие базы данных
В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engineи разверните его.
Разверните узел Базы данных и щелкните правой кнопкой мыши базу данных, которую нужно сжать.
В меню наведите указатель мыши на пункт Задачи, затем на пункт Сжать и выберите команду База данных.
База данных
Отображает имя выбранной базы данных.
Выделенное в данный момент место
Отображает суммарное используемое и неиспользуемое пространство для выбранной базы данных.
Доступное свободное место
Отображает суммарное свободное место для файлов журналов и данных в выбранной базе данных.
Реорганизовать файлы перед освобождением неиспользованного места
Установка данного флажка эквивалентна выполнению инструкции DBCC SHRINKDATABASE с заданием целевого процентного параметра. Снятие этого флажка равнозначно выполнению процедуры DBCC SHRINKDATABASE с параметром TRUNCATEONLY. По умолчанию при открытии диалогового окна этот флажок не установлен. Если этот флажок установлен, то пользователь должен задать целевое процентное значение.
Максимальное свободное пространство в файлах после сжатия
Введите максимальный процент свободного пространства, которое должно остаться в базе данных после ее сжатия. Допустимы значения от 0 до 99.
Щелкните ОК.
Использование Transact-SQL
Сжатие базы данных
Установите соединение с компонентом Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере инструкция DBCC SHRINKDATABASE используется для уменьшения размера данных и файлов журнала в базе данных UserDB и для выделения 10 процентов свободного пространства в базе данных.
Дальнейшие действия. После сжатия базы данных
Данные, перемещаемые в процессе сжатия файла, могут быть разбросаны по любым доступным местам в файле. Это вызывает фрагментацию индекса и может увеличить время выполнения запросов, выполняющих поиск в диапазоне индекса. Чтобы устранить фрагментацию, предусмотрите возможность перестроения индексов файла после сжатия.
Сжатие файла
В этом подразделе описывается сжатие данных или файла журнала в 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.
На панели «Стандартная» нажмите Создать запрос.
Shrink a Database
This article describes how to shrink a database in SQL Server by using Object Explorer in SQL Server Management Studio or Transact-SQL.
Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.
Limitations and restrictions
You cannot shrink a database while the database is being backed up. Conversely, you cannot backup a database while a shrink operation on the database is in process.
Recommendations
To view the current amount of free (unallocated) space in the database. For more information, see Display Data and Log Space Information for a Database
Consider the following information when you plan to shrink a database:
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation, and likely created the need for autogrowth events to reclaim the space, hindering performance.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
Permissions
Requires membership in the sysadmin fixed server role or the db_owner fixed database role.
Use SQL Server Management Studio
To shrink a database
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Expand Databases, and then right-click the database that you want to shrink.
Point to Tasks, point to Shrink, and then select Database.
Database
Displays the name of the selected database.
Current allocated space
Displays the total used and unused space for the selected database.
Available free space
Displays the sum of free space in the log and data files of the selected database.
Reorganize files before releasing unused space
Selecting this option is equivalent to executing DBCC SHRINKDATABASE specifying a target percent option. Clearing this option is equivalent to executing DBCC SHRINKDATABASE with TRUNCATEONLY option. By default, this option is not selected when the dialog is opened. If this option is selected, the user must specify a target percent option.
Maximum free space in files after shrinking
Enter the maximum percentage of free space to be left in the database files after the database has been shrunk. Permissible values are between 0 and 99.
Select OK.
Use Transact-SQL
To shrink a database
Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example uses DBCC SHRINKDATABASE to decrease the size of the data and log files in the UserDB database, and to allow for 10 percent free space in the database.
Follow Up: After you shrink a database
Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.
