Sql сжатие всех баз скрипт

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

Что такое сжатие в Microsoft SQL Server?

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

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

Наибольший эффект от сжатия достигается тогда, когда операция сжатия выполняется после операции удаления таблиц из БД или удаления данных из таблиц.

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

Усечение журнала транзакций происходит автоматически:

• В простой модели восстановления — после достижения контрольной точки, которая может возникнуть, например, после создания BACKUP базы данных, при явном выполнении инструкции CHECKPOINT, или тогда когда размер логического журнала транзакций заполняется на 70 процентов, во всех этих случаях происходит автоматическая очистка неактивной части журнала, т.е. его усечение;
• В модели полного восстановления или в модели восстановления с неполным протоколированием — после создания резервной копии журнала при условии, что с момента создания последней резервной копии журнала была достигнута контрольная точка.

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

Также возможно размер файлов журнала транзакций слишком большой (как при простой, так и при полной модели восстановления) за счет задержки процедуры усечения, т.е. размер журнала, состоит в основном из активной части журнала, а активную часть усечь нельзя, поэтому физический размер журнала растет. На задержку процедуры усечения влияют такие факторы как: активные длительные транзакции, некоторые сценарии отображения зеркальных баз данных и журнала транзакций, некоторые сценарии при репликации транзакций и журнала транзакций, а также усечение журнала невозможно во время операций резервного копирования и восстановления данных. В данном случае Вам нужно устранить причины задержки, затем сделать усечение (т.е. например, для полной модели восстановления BACKUP журнала), а затем сжатие до приемлемых размеров.

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

Как сжать базу данных в MS SQL Server?

Сжать файлы базы данных и журнала транзакций можно и с помощью графического интерфейса Management Studio и с помощью инструкций Transact-SQL: DBCC SHRINKDATABASE и DBCC SHRINKFILE. Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON.

Примечание! Сжатие базы данных я буду рассматривать на примере Microsoft SQL Server 2016 Express.

Сжимаем базу данных с помощью среды Management Studio

Запускаем Management Studio и в обозревателе объектов открываем объект «Базы данных». Затем щелкаем правой кнопкой мыши по БД, которую необходимо сжать, далее выбираем «Задачи ->Сжать -> База данных (или Файлы, если, например, нужно сжать только журнал транзакций)». Я для примера выбираю «База данных».

В итоге у Вас откроется окно «Сжатие базы данных», в котором Вы, кстати, можете наблюдать размер базы данных, а также доступное свободное место, которое можно удалить (т.е. сжать). Нажимаем «ОК».

Через некоторое время, в зависимости от размера базы данных, сжатие будет завершено.

Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE

В MS SQL Server для выполнения сжатия файлов базы данных и журнала транзакций существуют две инструкции SHRINKDATABASE и SHRINKFILE.

• DBCC SHRINKDATABASE – это команда для сжатия базы данных;
• DBCC SHRINKFILE – с помощью данной команды можно выполнить сжатие некоторых файлов базы данных (например, только журнала транзакций).

Для того чтобы выполнить сжатие БД (например, TestBase) точно также как мы это сделали чуть ранее в Management Studio, выполните следующую инструкцию.

SHRINKDATABASE имеет следующие параметры:

database_name или database_ >Синтаксис SHRINKDATABASE

Для того чтобы сжать только журнал транзакций можно использовать инструкцию SHRINKFILE, например.

В данном случае мы осуществим сжатие файла журнала (TestBase_log – это название файла журнала транзакций), до его начального значения, т.е. до значения по умолчанию. Для того чтобы сжать файл до определенного размера, укажите вторым параметром размер в мегабайтах. Например, следующей инструкцией мы уменьшим размер файла журнала транзакций до 5 мегабайт.

Также необходимо учесть, что если Вы укажете размер меньше того, чем требуется для хранения данных в файле, то файл до этого размера сжат не будет. Например, допустим, если Вы указали 5 мегабайт, а для хранения данных в файле требуется 7 мегабайт, файл будет сжат только до 7 мегабайт.

SHRINKFILE также имеет параметры NOTRUNCATE и TRUNCATEONLY.

Синтаксис SHRINKFILE

Рекомендации и важные моменты при сжатии базы данных

• Операция сжатия базы данных может вызвать фрагментацию индексов и замедлить работу БД. Поэтому слишком часто не рекомендуется выполнять сжатие базы данных;
• Сжимать БД лучше до операции перестроения индексов, т.е. после сжатия запустите процедуру перестроения индексов;
• Параметр базы данных AUTO_SHRINK (автоматическое сжатие) лучше не выставлять в значение ON, а оставлять по умолчанию, т.е. в OFF, если конечно у Вас нет на это достаточно серьезных оснований;
• Инструкция SHRINKDATABASE не позволяет уменьшить размер базы данных до размера, который меньше начального, т.е. минимального. Однако инструкция SHRINKFILE сделать это может (вторым параметром указываем размер меньше минимального). Минимальный размер базы данных — это размер, который указан при создании базы данных или явно установленный операцией изменения размера БД, такой как DBCC SHRINKFILE или ALTER DATABASE. Например, если база данных была создана с размером 10 мегабайт, потом увеличилась до 100 мегабайт, ее можно сжать с помощью SHRINKDATABASE только до начальных 10 мегабайт, даже если все данные были удалены из базы данных;
• Сжимать файлы базы данных и журнала транзакций нельзя, когда идет процесс их резервирования. И наоборот, создавать резервные копии базы и журнала транзакций нельзя пока идет процесс их сжатия;
• Выполнение инструкции DBCC SHRINKDATABASE без указания параметра NOTRUNCATE или TRUNCATEONLY равносильно выполнению инструкции DBCC SHRINKDATABASE с параметром NOTRUNCATE после выполнения инструкции DBCC SHRINKDATABASE с параметром TRUNCATEONLY;
• В процессе сжатия базы данных пользователи могут работать в ней (т.е. переводить БД в однопользовательский режим не нужно);
• В любой момент времени Вы можете прервать процесс выполнения операций SHRINKDATABASE и SHRINKFILE, при этом вся выполненная работа сохраняется;
• Перед запуском процедуры сжатия проверьте, есть ли свободное пространство для удаления в файлах базы данных, т.е. можно ли вообще сжать файлы, выполнив следующий запрос (он покажет в мегабайтах, на сколько Вы можете уменьшить файлы БД).

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

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

В этом подразделе содержатся инструкции по сжатию базы данных при помощи обозревателя объектов SQL Server 2019 (15.x) SQL Server 2019 (15.x) в среде SQL Server Management Studio SQL Server Management Studio или Transact-SQL Transact-SQL . This topic describes how to shrink a database by using Object in SQL Server 2019 (15.x) SQL Server 2019 (15.x) by using SQL Server Management Studio SQL Server Management Studio or Transact-SQL 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.

В этом разделе In This Topic

Перед началом работы Before you begin:

Сжатие базы данных с помощью: To shrink a database, using:

Дальнейшие действия. Сжатие базы данных Follow Up: You shrink a database

Перед началом Before You Begin

Ограничения Limitations and Restrictions

Размер базы данных нельзя сделать меньше минимального размера базы данных. The database cannot be made smaller than the minimum size of the database. Минимальный размер — это первоначальный размер, заданный при создании базы данных, или последний размер, явно установленный операцией изменения размера файла (например, DBCC SHRINKFILE). The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. Если, допустим, база данных была создана с размером 10 МБ и затем увеличилась до 100 МБ, ее можно сжать только до 10 МБ, даже если удалить из нее все данные. For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

Невозможно сжать базу данных во время создания ее резервной копии. 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.

Инструкция DBCC SHRINKDATABASE завершится с ошибкой при обнаружении оптимизированного для памяти xVelocity индекса columnstore. DBCC SHRINKDATABASE will fail when it encounters an xVelocity memory optimized columnstore index. Работа, выполненная до встречи с индексом columnstore, будет выполнена успешно, поэтому база данных может иметь меньший размер. Work completed before encountering the columnstore index will succeed so the database might be smaller. Чтобы выполнить инструкцию DBCC SHRINKDATABASE, отключите все индексы columnstore до ее запуска, а затем перестройте индексы columnstore. To complete DBCC SHRINKDATABASE, disable all columnstore indexes before executing DBCC SHRINKDATABASE, and then rebuild the columnstore indexes.

Рекомендации 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.

Операция сжатия не избавляет от фрагментации индексов в базе данных и обычно приводит к еще более сильной фрагментации. 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.

Не следует устанавливать параметр базы данных AUTO_SHRINK в значение ON без достаточных на то оснований. Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

безопасность Security

Permissions Permissions

Необходимо быть членом предопределенной роли сервера sysadmin или предопределенной роли базы данных db_owner . Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

Использование среды SQL Server Management Studio Using SQL Server Management Studio

Сжатие базы данных To shrink a database

В обозревателе объектовподключитесь к экземпляру компонента Компонент SQL Server Database Engine SQL Server Database Engine и разверните его. In Object Explorer, connect to an instance of the Компонент SQL Server Database Engine 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 click 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
Установка данного флажка эквивалентна выполнению инструкции DBCC SHRINKDATABASE с заданием целевого процентного параметра. Selecting this option is equivalent to executing DBCC SHRINKDATABASE specifying a target percent option. Снятие этого флажка равнозначно выполнению процедуры DBCC SHRINKDATABASE с параметром TRUNCATEONLY. 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. Допустимы значения от 0 до 99. Permissible values are between 0 and 99.

Нажмите кнопку ОК. Click OK.

Использование Transact-SQL Using Transact-SQL

Сжатие базы данных To shrink a database

Установите соединение с компонентом Компонент Database Engine Database Engine . Connect to the Компонент Database Engine Database Engine .

На панели «Стандартная» нажмите Создать запрос. From the Standard bar, click New Query.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере инструкция DBCC SHRINKDATABASE используется для уменьшения размера данных и файлов журнала в базе данных UserDB и для выделения 10 процентов свободного пространства в этой базе данных. This example uses DBCC SHRINKDATABASE to decreases 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.

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

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

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

Логическая фрагментация кластерного индекса перед сжатием равна близким к идеальным 0.4%.

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

Ого! После сжатия логическая фрагментация почти 100%. Операция сжатия полностью фрагментировала индекс, лишая любого шанса на эффективное сканирование диапазонов в этом индексе путем обеспечения ситуации, когда все упреждающие операции ввода-вывода со сканированием диапазона будут одностраничными операциями ввода-вывода.

Почему такое произошло? Операция сжатия файла данных работает с одним файлом за раз, и использует глобальную карта распределения (GAM) (смотрите статью «Внутри Storage Engine: GAM, SGAM, PFS и другие карты распределения», английский) чтобы найти самую последнюю страницу, размещенную в файле. Затем она перемещает эту страницу настолько близко к началу файла, насколько это возможно, и снова, и снова повторяет такую операцию. В ситуации выше, это полностью развернуло порядок кластерного индекса, сделав его из полностью дефрагментированного полностью фрагментированным.

Одинаковый код используется в командах DBCC SHRINKFILE, DBCC SHRINKDATABASE, и при автосжатии – они одинаково плохи. И вместе с фрагментацией индекса, сжатие файлов данных генерирует большое количество операций ввода/вывода, активно использует процессорное время и генерирует большое количество записей в журнале транзакций — поскольку все, что оно делает, полностью журналируется.

Сжатие файлов данных никогда не должно быть частью регулярного обслуживания, и вы НИКОГДА, НИКОГДА не должны включать автосжатие. Я пытался добиться его исключения из SQL Server 2005 и SQL Server 2008, когда я был в должности, позволяющей добиваться этого – единственная причина, почему оно еще есть — это обеспечение обратной совместимости. Не попадайтесь в ловушку создания плана обслуживания, который перестраивает все индексы и потом пытается освободить место, занятое при перестроении индексов, запуском сжатия — это игра с нулевой суммой, где все, что вы делаете — это генерируете записи в журнале транзакций с нулевой реальной пользой для производительности.

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

Я рекомендую следующий метод:

• Создайте новую файловую группу
• Переместите все вовлеченные таблицы и индексы в новую файловую группу, используя синтаксис CREATE INDEX … WITH (DROP_EXISTING = ON) ON, чтобы переместить таблицы и убрать фрагментацию из них одновременно
• Удалите старую файловую группу, которую вы все равно собирались сжимать (или сожмите ее по максимуму, если это первичная файловая группа)

На самом деле вам необходимо обеспечить дополнительное свободное пространство, прежде чем вы сможете сжать старые файлы, но это гораздо более чистый механизм.

Если у вас нет совершенно никакого выбора и вы должны запустить операцию сжатия файлов, будьте готовы к тому, что вы вызовете фрагментацию индексов и вы должны предпринять действия, чтобы убрать ее впоследствии если она вызовет проблемы с производительностью. Единственный способ убрать фрагментацию индекса без роста файла данных — это использование DBCC INDEXDEFRAG или ALTER INDEX … REORGANIZE. Эти команды требуют дополнительно одной страницы размером 8Кб, вместо необходимости построения полностью нового индекса в случае выполнения операции перестроения.

Итог – пытайтесь избегать запуска сжатия файлов любой ценой!