Ms sql shrink log file не работает

DBCC SHRINKFILE (Transact-SQL)

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

Имя столбца	Описание
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 в однопользовательском режиме.

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

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

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

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

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

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

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

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

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

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

Этот раздел описывает методы диагностики и устранения проблем, которые могут произойти при выполнении команды DBCC SHRINKFILE:

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

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

• Выполните следующий запрос.

• Выполните команду DBCC SQLPERF, чтобы освободить пространство, используемое журналом транзакций.

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

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

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

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

Такое сообщение означает, что операция сжатия блокируется транзакциями с моментальным снимком, отметка времени которого старше, чем 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 .

Примеры

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

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

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

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

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

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

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

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

Источник

Why Can’t I shrink log file in full recovery mode [duplicate]

I have a log file that is 302MB.

I have done a log backup which has left the log file mostly free (I can see this through the Disk Usage standard report)

If i try to run

the file is still showing as 302MB.

I know I can change the database to simple recovery, then run one of the commands above and set back to full recovery (and then do a full backup to make sure the database is not in Pseudo-simple recovery mode)

However, why can I not shrink the file in full recovery mode?

I know shrink isn’t something you should do, but in my real world database, since no one had ever backed up the transaction log it had grown to 30GB and now regular log backups have been put in place to prevent growth of this level

4 Answers 4

However, why can I not shrink the file in full recovery mode?

It’s working as designed. You just need to understand more about what is going on, so you can decide which is the appropriate action to take.

That, or you’re just trying to shrink it at the wrong time (see my note #7 below).

First of all, «log file» is a bit of a misnomer: SQL uses the transaction log as its «working space» for everything it does, so it will always be non-zero in size. In my experience, I normally expect the tran log to be between 10% and 25% the size of the data files, depending on what recovery mode you’re using, and the kind of activity on the database. You didn’t mention the size of your data file, so I can’t judge whether 302MB is big (it frankly doesn’t sound very big to me).

Regarding recovery mode, the key question here is: if you have to recover the database (due to an emergency), will you need «point-in-time» recovery? Or is a restore to the latest full backup sufficient?

Putting aside some unusual situations (replication/CDC/mirroring/availability groups), your recovery mode choice is more of a business decision than it is a technical one. In either mode, SQL server uses the transaction log for in-process transactions, the difference is what happens next:

• FULL recovery mode: When a transaction completes, it sticks around in the tran log until you perform a transaction log backup. This tends to make the log larger (depending on how often you run the log backups), but means you can do a «point-in-time» recovery in the event of a disaster: you can restore the latest full backup, then use the tran log backups to «roll forward» to just before the disaster occurred.
• SIMPLE recovery mode: When a transaction completes, the space it used in the tran log is immediately available for re-use by later transactions. This keeps the transaction log file (comparatively) small, but means that your only choice in the event of a disaster is to restore from the latest full backup.

In some companies, you’ll use FULL for all production databases, and SIMPLE for all QA/DEV environments. Or perhaps some specific databases, like ones used for reporting and/or data processing don’t need full recovery because you can simply re-run the prior day’s processing to get back up to date. But like I said, that’s a business decision/risk analysis question, not really a technical one.

So, if you don’t need point-in-time recovery, then switch it to simple mode, and leave it that way.

If you do need point-in-time recovery, then switch it to full mode, and run regular tran log backups throughout the day, according to your business risk/recovery needs. Hourly is popular, but some people recommend them far more often.

One final point that I alluded to before: once you’ve selected the correct recovery mode, you might still need to shrink an over-sized log. A few things to keep in mind:

1. Again, make sure you’ve chosen the right recovery model
2. If you’re running in FULL mode, make sure you’re running frequent tran log backups
3. Choose a reasonable target size for the log, never try to shrink it to zero. I typically scale it based on the total size of the data files (or at least the used space within the data files)
4. Read and understand this important advice about VLF size. This might provide more guidance about deciding on a reasonable log size, or give you a better method for getting your log to that size.
5. Never use SHRINKDATABASE , always use SHRINKFILE
6. In both simple and full mode, there is a chance that the end of the log file is currently being used by SQL for an active transaction. If that’s the case, then it won’t shrink as much as you want. Just shrink it again a few minutes later, and if the transaction is complete, it should work better the second time.
7. With a particularly stubborn tran log in full recovery mode, I’ve had luck doing SHRINKFILE , immediately followed by a transaction log backup, immediately followed by another SHRINKFILE .

Источник