Так как все логические файлы журналов расположенные в конце файла находятся в использовании

Обновлено: 06.07.2024

Im конвертирует некоторые исторические базы данных в режим "только для чтения" и пытается их очистить. Id нравится сокращать транзакционные журналы до 1 МБ. Я понимаю, что обычно рассматриваемая плохая практика сокращает транзакционные журналы, но я думаю, что это, вероятно, исключение из правила.

Базы данных устанавливаются на SIMPLE-восстановление в стандарте SQL Server 2012. Поэтому я ожидал, что после выдачи инструкции CHECKPOINT, что содержимое журнала транзакций может быть сокращено, но это не работает.

Вручную выдавать команды CHECKPOINT.
Отсоединение/прикрепление файлов.
Резервное копирование/восстановление базы данных.
Переключение с простого на полный, обратно на простое восстановление.
Яростно встряхиваю кулаком по нему.

После каждой из попыток я попытался запустить:

DBCC SHRINKFILE (N'MYDATABASE_2010_log ', 0)
DBCC SHRINKFILE (N'MYDATABASE_2010_log ', 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'MYDATABASE_2010_log ', 1)

Невозможно сжать файл журнала 2 (MYDATABASE_2010_log), поскольку общее количество логические файлы журнала не могут быть меньше 2.

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

Ниже приведены результаты DBCC SQLPERF (LOGSPACE)

Ниже приведены результаты DBCC LOGINFO:

Кто-нибудь знает, что я делаю неправильно?

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

Вы можете фильтровать по имени базы данных

Если значение log_reuse_wait равно 0 , журнал базы данных может быть усечен. Если значение отличное от 0, то есть что-то, что позволяет избежать усечения. См. Описание значений ожидания повторного использования журнала в документах для sys.databases. Или даже лучше здесь: Факторы, которые могут задержать усечение журнала. Если значение 1 , вы можете дождаться контрольной точки или запустить ее вручную: CHECKPOINT.

Как только вы проверили, что нет причины, позволяющей избежать усечения файла журнала, вы можете выполнить обычную последовательность резервного копирования (журнал, полный инкрементного) и DBCC SHRINKDATABASE или DBCC SHRINKFILE . И файл должен сжиматься или нет.

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

Файл журнала работает как циклический буфер и может быть усечен только путем удаления конца файла. Если используемая часть кругового буфера находится в конце файла, то она не может быть усечена. Вам просто нужно подождать до тех пор, пока не будет использована используемая часть журнала транзакций, и переместится от конца файла к началу файла. Как только это произойдет, вы можете запустить одну из команд сокращения, и ваш файл будет сжиматься без сбоев. Это очень хорошо объяснено на этой странице: Как сжать журнал SQL Server.

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

У меня есть база данных с файлом журнала транзакций 28gig. Режим восстановления прост. Я просто взял полную резервную копию базы данных, а затем побежал так:

backup log dbmcms with truncate_only

DBCC SHRINKFILE ('Wxlog0', TRUNCATEONLY)

имя БД-db_mcms, а имя файла журнала транзакций-Wxlog0.

Не помогло. Я не знаю, что делать дальше.

спасибо всем за ответы.

мы, наконец, нашли проблему. В sys.databases, log_reuse_wait_desc был равен "репликации". По-видимому, это означает, что SQL Server ожидает завершения задачи репликации, прежде чем сможет повторно использовать пространство журнала.

репликация ~~никогда не использовался на этом БД или сервер~~ когда-то играл с этим db. Мы очистили неправильное состояние, запустив sp_removedbreplication. После того, как мы запустили это, backup log и dbcc shrinkfile работали нормально.

определенно один для мешка хитростей.

вы можете столкнуться с этой проблемой, если ваша база данных настроена на автозапуск журнала, и вы получите много виртуальных файлов журнала.
Запустить DBCC LOGINFO('databasename') & посмотрите на последнюю запись, если это 2, то ваш файл журнала не будет сжиматься. В отличие от файлов данных файлы виртуального журнала нельзя перемещать внутри файла журнала.

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

для дополнительных бонусных очков запустите DBBC LOGINFO между журналом & увиливает от

'sp_removedbreplication' не решил проблему для меня, так как SQL только что вернулся, сказав, что база данных не была частью репликации.

Я нашел свой ответ здесь:

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

вам это не нужно

просто убедитесь, что вы осознаете опасности: смотрите здесь:не обрезать файлы ldf!

этот ответ был снят с здесь и публикуется здесь, если другой поток удаляется:

проблема в том, что у вас есть не распределенный LSN в журнале. Я видел это однажды раньше, не уверен, почему мы не снимаем отметку транзакции реплицируются. Мы проведем внутреннее расследование. Вы можно выполнить следующую команду, чтобы отменить отметку транзакции как replicated

в этот момент Вы должна быть возможность обрезать журнал.

У меня была такая же проблема в прошлом. Обычно сжатие и резервное копирование trn должны происходить несколько раз. В крайних случаях я устанавливаю БД на "простое" восстановление, а затем запускаю операцию сжатия файла журнала. Это всегда срабатывает. Однако недавно у меня была ситуация, когда это не сработало бы. Проблема была вызвана длительным запросом, который не был завершен, поэтому любые попытки сокращения были бесполезны, пока я не смог убить этот процесс, а затем запустить мои операции сокращения. Мы говорим журнала файл, который вырос до 60 ГБ и теперь уменьшен до 500 МБ.

помните, как только вы измените с полного на простой режим восстановления и сделать сокращение, не забудьте установить его обратно в полный. Затем сразу после этого вы должны сделать полную резервную копию БД.

Если вы установите режим восстановления в базе данных в 2005 году (не знаю, до 2005 года), он отбросит файл журнала все вместе, а затем вы можете вернуть его в режим полного восстановления, чтобы перезапустить/воссоздать файл журнала. Мы столкнулись с этим с SQL 2005 express в том, что мы не могли приблизиться к пределу 4GB с данными, пока мы не изменили режим восстановления.

Я знаю, что это несколько лет, но хотел бы добавить некоторую информацию.

Я нашел в очень больших журналах, в частности, когда БД не была настроена на резервное копирование журналов транзакций (журналы были очень большими), первая резервная копия журналов не устанавливала log_reuse_wait_desc ни на что, но оставляла статус все еще резервного копирования. Это заблокирует мозгоправа. Запуск резервной копии во второй раз правильно сбросит log_reuse_wait_desc ни к чему, позволяя сжатию обрабатывать.

вы пробовали из среды SQL Server management studio с GUI. Щелкните правой кнопкой мыши по базе данных, задачам, сжатию, файлам. Выберите тип файла-журнал.

Я работал на себя неделю назад.

попробуйте создать другую полную резервную копию после резервного копирования журнала w / truncate_only (IIRC вы должны сделать это в любом случае, чтобы поддерживать цепочку журналов). В простом режиме восстановления ваш журнал не должен сильно расти, так как он эффективно усекается после каждой транзакции. Затем попробуйте указать размер файла журнала, например

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

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

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

DBCC SHRINKFILE ('Wxlog0', 1)

и проверьте это на статьи:

вы можете попытаться переместить выделенные страницы в начало файла журнала сначала с помощью

DBCC SHRINKFILE ('Wxlog0', NOTRUNCATE)

DBCC SHRINKFILE ('Wxlog0', 1)

верните БД в полный режим, запустите резервную копию журнала транзакций (а не только полную резервную копию), а затем уменьшите.

после того, как он сжат, вы можете вернуть БД в простой режим, и его журнал txn останется того же размера.

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

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

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

Я создал резервную копию журнала транзакций, а затем продолжил сжимать журнал транзакций .файл LDF. Однако журнал транзакций не уменьшился вообще.

затем я выдал "контрольную точку", а затем" DBCC DROPCLEANBUFFER " и смог сжать журнал транзакций .ldf файл после

Странно. 1С с фоновыми заданиями не мешается в этой базе?

Фоновые отключены давно, база создана из рабочей базы. Я вычитал, что проблема может быть в этом log_reuse_wait_desc REPLICATION, но я не знаю как это изменить, да и мне не ясно что это такое, у меня небольшой опыт администрирования на уровне MSSQL

Тупо в свойствах базы уменьшаете размер ldf и нажимаете Ок. Не надо никаких команд писать. Шринкнется само.

все простые действия я делал: результат минимальный, ну не может лог быть больше в 4 раза самой базы после бэкапа

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

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

еще раз, не надо делать симпл. Сделай бэкап лога и шринк.

То есть. Сделали симпл, нажали Ок. Открыли еще раз - уменьшили файл до 1 мегабайта - нажали Ок. И что?

А так. делаешь план обслуживания, в задачу пихаешь бэкап лога и стрелочкой - инструкцию t-sql на шринк лога. Всё

какой план обслуживания - если вручную не шринкуется. Может есть какие команды, чтобы посмотреть возможные причины?

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

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

а что значит "включена репликация", может кто пояснить? Это не бэкапы по регламенту FULL и ЖТ, а что-то другое?

Бэкап лог в нулл, шринк лог. Повторить два раза. Профит.

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

у мну правда фул мода, поэтому для упыпырища шринк: ALTER DATABASE [upp] SET RECOVERY SIMPLE go DBCC SHRINKFILE ([upp_log], 1); ALTER DATABASE [upp] SET RECOVERY FULL go для простой моды достаточно будет:

полный бэкап сделайте. После этого всё отлично шринкуется

Можно еще детач-аттач сделать (но без лога) - тогда создастся новый лог.

Редкий случай для любого форума - автор темы задал хорошо сформулированный, хотя и нетривиальный, вопрос. Затем сам нашел верное решение, не забыл его описать в 22, но продолжает получать бесполезные советы.

У меня коллега книжечка где-то валялась, «SQL сервер для самых маленьких», так там об этом неписано где-то в начале, как обслуживать СУБД с помощью конструкторов. Там еще текст можно сгенерить и картиночки двигать.

у меня статистики маловато, но ни разу не встречал кого то, кто включал бы на MS 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 не поддерживают этот параметр.

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

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

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

В случае сбоя операции DBCC SHRINKFILE возникает ошибка.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Разрешения

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

Примеры

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

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

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

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

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

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

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

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

Читайте также: