DBCC SHRINKFILE (Transact-SQL)

  • Статья

Применимо к:база данныхSQL Server Azure SQL Управляемый экземпляр SQL Azure

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

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

Синтаксис

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { 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 }

Примечание

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

Аргументы

file_name

Логическое имя файла, предназначенного для сжатия.

file_id

Идентификационный номер (идентификатор) файла, предназначенного для сжатия. Чтобы получить идентификатор файла, используйте системную функцию FILE_IDEX или выполните запрос к представлению каталога sys.database_files в текущей базе данных.

target_size

Целое число, представляющее новый размер файла в мегабайтах. Если значение не указано или 0, DBCC SHRINKFILE уменьшается до размера создания файла.

Размер пустого файла по умолчанию можно уменьшить с помощью DBCC SHRINKFILE <target_size>. Например, при создании файла с размером 5 МБ и последующем уменьшении размера до 3 МБ, в то время как файл остается пустым, размер файла по умолчанию задается равным 3 МБ. Это правило применимо только к пустым файлам, в которых никогда не содержались данные.

Контейнеры файловых групп FILESTREAM не поддерживают этот параметр.

Если этот параметр задан, DBCC SHRINKFILE пытается сжать файл до target_size. Используемые страницы в освобождаемой области файла перемещаются в свободное пространство в сохраняемых областях файла. Например, при использовании файла DBCC SHRINKFILE данных размером 10 МБ операция с 8 target_size перемещает все использованные страницы в последних 2 МБ файла в любые нераспределенные страницы в первых 8 МБ файла. DBCC SHRINKFILE не сжимает файл после необходимого размера хранимых данных. Например, если используется 7 МБ файла данных размером 10 МБ, DBCC SHRINKFILE инструкция с target_size 6 сжимает файл только до 7 МБ, а не до 6 МБ.

EMPTYFILE

Переносит все данные из указанного файла в другие файлы в той же файловой группе. Другими словами, EMPTYFILE переносит данные из указанного файла в другие файлы в той же файловой группе. EMPTYFILE гарантирует, что в файл не добавляются новые данные, несмотря на то, что этот файл не доступен только для чтения. Для удаления файла можно использовать инструкцию ALTER DATABASE. Если вы используете инструкцию ALTER DATABASE для изменения размера файла, флаг только для чтения сбрасывается и можно добавить данные.

Для контейнеров файловых групп FILESTREAM нельзя использовать для ALTER DATABASE удаления файла, пока сборщик мусора FILESTREAM не запустит и не удалит все ненужные файлы контейнера файловой группы, скопированные EMPTYFILE в другой контейнер. Дополнительные сведения см. в разделе sp_filestream_force_garbage_collection. Сведения об удалении контейнера FILESTREAM см. в соответствующем разделе в статье Параметры инструкции ALTER DATABASE для файлов и файловых групп (Transact-SQL)

NOTRUNCATE

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

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

Контейнеры файловых групп FILESTREAM не поддерживают этот параметр.

TRUNCATEONLY

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

target_size игнорируется, если указан параметр .TRUNCATEONLY

Параметр TRUNCATEONLY не перемещает сведения в журнале, но удаляет неактивные VLF из конца файла журнала. Контейнеры файловых групп FILESTREAM не поддерживают этот параметр.

WITH NO_INFOMSGS

Подавляет вывод всех информационных сообщений.

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

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

При выполнении команды сжатия в режиме 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 ]

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

  • SELF

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

  • BLOCKERS

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

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

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

Имя столбца Описание
DbId Идентификатор базы данных, файл которой компонент Компонент Database Engine пытался сжать.
FileId Идентификационный номер файла, сжатие которого было предпринято компонентом Компонент Database Engine.
CurrentSize Количество 8-килобайтных страниц, занятых файлом в настоящее время.
MinimumSize Минимальное количество 8-килобайтных страниц, которое может занимать файл. Это число соответствует минимальному размеру файла при его создании.
UsedPages Количество 8-килобайтных страниц, используемых файлом в настоящее время.
EstimatedPages Количество 8-килобайтных страниц, до которого можно было бы сжать файл по оценке компонента Компонент Database Engine.

Комментарии

DBCC SHRINKFILE применяется к файлам текущей базы данных. Дополнительные сведения об изменении текущей базы данных см. в статье USE (Transact-SQL).

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

При сбое DBCC SHRINKFILE операции возникает ошибка.

Другие пользователи могут работать в базе данных во время сжатия файла; база данных не должна находиться в однопользовательском режиме. Для сжатия системных баз данных не обязательно запускать экземпляр SQL Server в однопользовательском режиме.

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

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

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

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

Сжатие файла журнала

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

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

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

Примите во внимание следующие сведения при планировании сжатия файла.

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

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

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

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

Диагностика

В этом разделе описывается диагностика и устранение проблем, которые могут возникнуть при выполнении DBCC SHRINKFILE команды.

Файл не сжимается

Если размер файла не изменяется после сжатия, которое было выполнено без ошибок, проверьте, есть свободное место в файле, с помощью следующей команды:

  • Выполните следующий запрос.
SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;
  • Выполните команду DBCC SQLPERF, чтобы освободить пространство, используемое журналом транзакций.

Если свободного пространства недостаточно, сжатие не поможет уменьшить размер файла.

Чаще всего результаты сжатия незаметны для файлов журнала. Такая несжимаемость характерна для неусеченных файлов журнала. Чтобы усечь журнал, можно задать для модели восстановления базы данных значение SIMPLE или создать резервную копию журнала, а затем снова выполнить DBCC SHRINKFILE операцию.

Операция сжатия блокируется

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

DBCC SHRINKFILE for file ID 1 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 динамическое административное представление содержит значение 15. Если столбец transaction_sequence_num или first_snapshot_sequence_num в представлении содержит меньшее число, чем последняя транзакция, выполненная операцией сжатия (109), то операция сжатия будет ждать завершения этих транзакций.

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

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

Разрешения

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

Примеры

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

В приведенном ниже примере файл данных с именем DataFile1 в пользовательской базе данных UserDB сжимается до 7 МБ.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Б. Сжатие файла журнала до указанного целевого размера

В следующем примере файл журнала в базе данных AdventureWorks2022 сжимается до 1 МБ. Чтобы позволить команде DBCC SHRINKFILE сжать файл, сначала усекается, задав для модели восстановления базы данных значение SIMPLE.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

В. Усечение файла данных

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

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

Г. Очистка файла

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

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

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

В приведенном ниже примере выполняется попытка сжатия файла данных в текущей пользовательской базе данных до 1 МБ. Выполняется запрос к представлению каталога sys.database_files для получения file_id файла данных, в этом примере file_id 5. Если блокировка не может быть получена в течение одной минуты, операция сжатия прервется.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

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

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