DBCC SHRINKDATABASE (Transact-SQL)

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH 

    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
         
         [ , NO_INFOMSGS]
    }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
  ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Синтаксис для Azure Synapse Analytics:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

Аргументы

database_name | database_id | 0

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

target_percent

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

NOTRUNCATE

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

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

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

TRUNCATEONLY

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

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

WITH NO_INFOMSGS

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

WAIT_AT_LOW_PRIORITY с операциями сжатия

Применимо к: SQL Server 2022 (16.x) и более поздних версий, Azure SQL Database, Управляемый экземпляр SQL Azure

Возможность ожидания с низким приоритетом снижает состязание за блокировку. Дополнительные сведения см. на странице Основные сведения о проблемах параллелизма с DBCC SHRINKDATABASE.

Эта возможность похожа на WAIT_AT_LOW_PRIORITY с операциями индексирования в сети с некоторыми различиями.

• Нельзя указать параметр ABORT_AFTER_WAIT NONE.

WAIT_AT_LOW_PRIORITY

При выполнении команды сжатия в WAIT_AT_LOW_PRIORITY режиме новые запросы, требующие блокировки стабильности схемы (Sch-S), не блокируются операцией ожидания сжатия до тех пор, пока операция сжатия не перестанет ждать и не начнет выполняться. Операция сжатия будет выполняться, когда она сможет получить блокировку изменения схемы (Sch-M). Если новая операция сжатия в WAIT_AT_LOW_PRIORITY режиме не может получить блокировку из-за длительного запроса, по умолчанию время ожидания операции сжатия истекает через 1 минуту и завершится без ошибок.

Если новая операция сжатия в WAIT_AT_LOW_PRIORITY режиме не может получить блокировку из-за длительного запроса, по умолчанию время ожидания операции сжатия истекает через 1 минуту и завершится без ошибок. Это произойдет, если операция сжатия не может получить блокировку Sch-M из-за параллельных запросов или запросов, содержащих блокировки Sch-S. По истечении времени ожидания сообщение об ошибке 49516 будет отправлено в журнал ошибок SQL Server, например: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. На этом этапе можно просто повторить операцию сжатия в WAIT_AT_LOW_PRIORITY режиме , зная, что это не повлияет на приложение.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

• SELF

  SELF — параметр по умолчанию. Прекратить текущую операцию сжатия базы данных без выполнения какого-либо действия.

• BLOCKERS

  Остановить все пользовательские транзакции, в данный момент блокирующие операцию сжатия базы данных, чтобы можно было продолжить данную операцию. Параметр BLOCKERS требует, чтобы имя входа было иметь ALTER ANY CONNECTION разрешение.

Наборы результатов

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

Remarks

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

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

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

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

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

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

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

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

При указании WAIT_AT_LOW_PRIORITY запрос на блокировку Sch-M операции сжатия будет ждать с низким приоритетом при выполнении команды в течение 1 минуты. Если операция заблокирована на время, будет выполнено указанное действие ABORT_AFTER_WAIT.

Принцип работы DBCC SHRINKDATABASE

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

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

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

Предположим, что файл данных базы данных mydb содержит 7 МБ данных. При задании значения 30 для target_percent можно сжать этот файл данных до 30 %. Однако указание target_percent 40 не сжимает файл данных, так как в текущем общем размере файла данных не удается создать достаточно свободного места.

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

Для файлов журнала ядро СУБД используетtarget_percent, чтобы вычислить целевой размер всего журнала. Вот почему target_percent — это количество свободного пространства в журнале после операции сжатия. Целевой размер всего журнала затем пересчитывается в целевой размер каждого файла журнала.

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

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

Общие сведения о проблемах с параллелизмом в DBCC SHRINKDATABASE

Команды сжатия базы данных и сжатия файлов могут привести к проблемам с параллелизмом, особенно с активным обслуживанием, таким как перестроение индексов, или к проблемам в занятых средах OLTP. Когда приложение выполняет запросы к таблицам базы данных, эти запросы будут получать и поддерживать блокировку стабильности схемы (Sch-S), пока запросы не завершат свои операции. При попытке освободить место во время регулярного использования сжатие базы данных и сжатие файлов сейчас требуют блокировки изменения схемы (Sch-M) при перемещении или удалении страниц карты распределения индекса (IAM), блокирующих блокировки Sch-S, необходимые для запросов пользователей. В результате длительные запросы блокируют операцию сжатия до завершения запросов. Это означает, что все новые запросы, требующие блокировки Sch-S, также помещаются в очередь за операцией сжатия в ожидании, и кроме этого, они будут заблокированы, что еще больше усугубит эту проблему параллелизма. Это может значительно повлиять на производительность запросов приложений, а также вызвать трудности при выполнении необходимого обслуживания для сжатия файлов базы данных. Появилась в SQL Server 2022 (16.x), функция ожидания сжатия с низким приоритетом (WLP) решает эту проблему, принимая блокировку изменения схемы в WAIT_AT_LOW_PRIORITY режиме . Дополнительные сведения см. на странице WAIT_AT_LOW_PRIORITY с операциями сжатия.

Дополнительные сведения о блокировках Sch-S и Sch-M см. в руководстве по блокировке и управлению версиями строк транзакций.

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

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

• Наибольший эффект от операции сжатия достигается при ее применении после операции, создающей неиспользуемое пространство, например после усечения таблицы или удаления таблицы.
• Большинству баз данных требуется некоторое свободное пространство для выполнения обычных ежедневных операций. Если сжатие файла базы данных производится регулярно, но он снова увеличивается в размерах, это означает, что для нормальной работы необходимо свободное пространство. В таких случаях повторное сжатие файла базы данных бессмысленно. События автоматического увеличения, необходимые для увеличения файла базы данных, снижают производительность.
• Операция сжатия не исключает фрагментацию индексов в базе данных и даже, наоборот, приводит к усилению фрагментации. Это еще одна причина, по которой не стоит выполнять регулярное сжатие базы данных.
• Если у вас нет определенного требования, не устанавливайте AUTO_SHRINK для параметра базы данных значение ON.

Диагностика

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

DBCC SHRINKDATABASE for database ID 9 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

Эта ошибка означает, что транзакции моментальных снимков с отметками времени старше 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). В этом случае операция сжатия будет ждать завершения этих транзакций.

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

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

Разрешения

Необходимо быть членом предопределенной роли сервера sysadmin или предопределенной роли базы данных db_owner .

Примеры

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

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

DBCC SHRINKDATABASE (UserDB, 10);
GO

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

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

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

В. Сжатие базы данных Azure Synapse Analytics

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

Г. Сжатие базы данных с помощью WAIT_AT_LOW_PRIORITY

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

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

См. также раздел

• Рекомендации по настройке автоувеличения и автосжатия в SQL Server
• Файлы и файловые группы базы данных
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)

Следующие шаги

• ALTER DATABASE (Transact-SQL)
• DBCC SHRINKFILE (Transact-SQL)
• Сжатие базы данных
• Сжатие файла