Sql 2008 как обрезать лог файл
Sql server - Как правильно сделать шринк лога транзакций MS SQL 2008? | GeekBrains
У меня на системном жестком диске стремительно заканчивается место. Я проанализировал проблему. Помимо прочего, обнаружил, что сильно выросли журналы транзакций.
Я нашел несколько дельных советов, как делается обрезка логов SQL 2008, но все советы предлагают выполнять ее вручную. В списке задач (правый клик по базе данных, "Задачи" - потом "Сжать"-"Файлы", там выбирается тип файла: журнал, и "Реорганизовать страницы").
Сжать у меня, вроде бы, получилось. Но остались несколько вопросов.
Во-первых, там предлагают сначала в свойствах базы данных сменить модель восстановления с "Полная" на "Простая". Потом уже в MS SQL обрезать лог транзакций SQL, а затем вернуть полную модель восстановления. Я не понял, зачем это делается перед сжатием. Это необходимо делать? Что будет, если этого не сделать?
И еще я слышал, что можно каким-то запросом SQL обрезать журнал транзакций SQL. Но примеров такого запроса не нашел. Как это сделать?
Шринк (Shrink) лога транзакций MS SQL 2008/2012
Когда при подключении к базе MS SQL появляются ошибки:Ошибка СУБД:
Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных «ReportServer» заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1
или
Ошибка СУБД:
Microsoft OLE Provider for SQL Server: The transaction log for database “ReportServer” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column is sys.database
HRESULT=80040E14, SQLSTATE=4 2000, native=9002
это значит, что на диске, где расположен лог транзакций закончилось место и теперь СУБД некуда записывать данные о новых транзакциях. Чаще всего такое происходит, когда не установлено никаких ограничений на размер лога и в MS SQL не создано соответствующих планов обслуживания.
В таком случае нужно уменьшить размер самого файла транзакций (*.ldf), другими словами сделать шринк (сжатие) лога. Для этого можно использовать как запрос, так и сжатие лога вручную.
Рассмотрим сжатие лога транзакций вручную:
Шаг 1. Установить модель восстановления Простая (Simple). Правой кнопкой на базе — Свойства(Properties)
Далее: Параметры(Options) — 4-й сверху пункт Модель восстановления(Recovery model) — Простая(Simple) — OK.
Выполнить сжатие (Shrink) лога транзакций. Правой кнопкой на базе — Задачи(Tasks) — Сжать(Shrink) — Файлы(Files) 
Установить Тип файла(File type) — Журнал(Log) — в Операция сжатия(Shrink action) — выбрать Реорганизовать страницы, перед тем освоить неиспользуемое место(Reorganize pages before releseasing unused space) — Сжать файл (Shrink file to)
указать приемлемый размер лога.
Установить модель восстановления Полная(Full). Правой кнопкой на базе — Свойства(Properties) — Параметры(Options) — 4-й сверху пункт Модель восстановления(Recovery model) — Полная(Full) — OK.
Вконтакте
Одноклассники
Мой мир
SQL обрезать логи: boot_from_cd — LiveJournal
Наши добрые друзья 1с-ники обрадовали забитым вусмерть логом sql. Проблема знакомая, хоть здесь, хоть по exchange с остановкой базой без бэкапов.Алгоритм возникновения проблемы следующий:
1. Ставим MSSQL, хоть SQL 2008, хоть SQL 2012 как у меня.
2. В свойствах датабазы выставлен recovery full.
3. Логи начинают разрастаться. Если выложены на отдельном диске, забивают насмерть весь диск, после чего sql останавливается.
Решение экстренное:
1. База => properties => options => recovery model => выставить simple.
2. База => tasks => shrink => files => shrink action => reorganize pages before releasing unesed space => shrink file to: => выставить размер до которого произойдет shrink имеющегося => ok
3. База => properties => options => recovery model => выставить full (если оно надо, конечно).
Ограничить размер файла логов:
1. База => properties => files => имя лога => autogrowth /maxsize => ... Maximum file size => По дефолту там 2 тб, выставить нужный размер.
2. При заполнении выставленного размера sql остановится. Поэтому ставим auto shrink => база => options => automatic => auto shrink => true.
Регламентом тоже можно.
Management => maintenance plans => reglament => далее по вкусу обрезку.
Как усечь журнал транзакций MSSQL Server?
Журнал транзакций во всех версиях Microsoft SQL Server имеет тенденцию расти со временем. Иногда он может заполнить все свободное место на сервере. Чтобы избежать этого, в SQL Server есть операция усечения журнала транзакций.
Журналы транзакций SQL Server и модель восстановления базы данных
Журналы транзакций используются для записи всех транзакций перед передачей данных в файл базы данных. Файлы журнала транзакций необходимы для отката базы данных до предыдущего состояния. Как правило, в журнале хранится порядковый номер, тип изменения, выполненные операции и т. д. Скорее всего, вы можете использовать SQL Server и без журнала транзакций, но тогда в в случае сбоя возможна потеря базы данных.
Журнал транзакций состоит из небольших логических элементов, называемых VLF (Virtual Log File). Вы можете узнать их количество, выполнив следующий запрос в контексте базы данных SQL Server:
DBCC LOGINFO
Количество возвращаемых строк указывает на то, сколько виртуальных файлов сегментировано в журнале. В поле «Status» отображается текущее состояние сегмента. Значение 0 означает, что сегмент в настоящее время не занят и может использоваться. 2 означает, что сегмент используется. Если свободных сегментов нет и в настройках базы данных SQL Server разрешено увеличение журнала транзакций, он будет увеличен и будут созданы новые VLF. Если размер журнала транзакций фиксирован или на диске недостаточно места, все операции по изменению структуры базы данных или ее содержимого станут недоступными. Скорее всего, вы получите ошибку: Журнал транзакций для базы данных переполнен.
Файлы журнала усекаются автоматически, в зависимости от модели восстановления, используемой в настройках SQL Server:
- Простая модель восстановления – файлы журнала автоматически обрезаются после достижения контрольной точки (самый простой вариант, требующий администрирования базы данных). При использовании модели простого восстановления журнал транзакций очищается сразу после завершения транзакции. В этом режиме вы можете откатить вашу базу данных только до времени полного резервного копирования базы данных.
- Модель полного восстановления – журнал транзакций не будет очищен, пока не будет завершено резервное копирование журнала транзакций. Этот режим обеспечивает наилучшую возможность восстановления данных после сбоя. В полном режиме журнал транзакций (LDF) может увеличиваться (поскольку изменения базы данных накапливаются в этом журнале). В модели полного восстановления все транзакции SQL записываются в файлы журнала на диске и сохраняются там до создания резервной копии. Хранение журналов позволяет вам при необходимости вернуться к более ранней копии базы данных, и вы можете выполнить восстановление для каждой транзакции.
- С неполным протоколированием – этот режим позволяет сократить занимаемое пространство используя минимальные настройки ведения журнала. Если журнал был поврежден или с момента создания последней резервной копии журналов выполнялись операции с неполным протоколированием, все изменения после этого резервного копирования необходимо внести повторно. Иначе результаты работы потеряны не будут.
Когда используется модель полного восстановления базы данных, необходимо регулярно выполнять резервное копирование журнала транзакций, в противном случае он будет чрезмерно увеличиваться до тех пор, пока не займет все место на диске, и SQL Server не начнет сообщать об ошибке.
Как обрезать журналы транзакций на MS SQL Server?
Мы получили вот такое сообщение:
Microsoft OLE DB Provider for SQL Server: Журнал транзакций для базы данных “buh” заполнен. Чтобы обнаружить причину, по которой место в журнале не может быть повторно использовано, обратитесь к столбцу log_reuse_wait_desc таблицы
sys. databases HRESULT=80040E14, SQLStvr: Error state=2, Severity=11,native=9002, line=1
Это означает, что на диске, где хранится журнал транзакций SQL, недостаточно места, и SQL не может записать новые данные транзакции. В этом случае вы можете обрезать файлы журналов SQL вручную (с помощью графического интерфейса Management Studio).
Чтобы обрезать журналы транзакций SQL, запустите SQL Server Management Studio (SSMS), выберите нужную базу данных, щелкните ее правой кнопкой мыши и выберите «Свойства» в контекстном меню. Перейдите в Параметры и переключите модель восстановления базы .
Меняем модель восстановления с Полной на Простую
Затем в главном меню перейдите в раздел «Задачи» -> «Сжать» -> «Файлы» .
В поле Тип файла выберите Журнал, в поле Имя файла укажите имя файла журнала. В поле «Операции сжатия» выберите « Реорганизовать страницы, перед тем как освободить неиспользуемое пространство» , установите нужный размер файла и нажмите «OK».
Нажимаем «OK». После завершения операции обязательно измените режим восстановления базы данных обратно на Полный.
Это только один из способов быстрого уменьшения размера логов. Не самый лучший, но очень простой и эффективный.
Кроме того, имейте в виду, что при усечении журналов транзакций SQL с помощью этого руководства все предыдущие резервные копии, сделанные в модели полного восстановления, могут быть отброшены. Вот почему этот метод следует использовать только в критических случаях, когда невозможно быстро очистить дисковое пространство другими способами.
MS SQL очистка журнала транзакций
В MS SQL очистка журнала транзакций необходима в том случае, если настроена полная модель восстановления базы данных. Если журнал транзакций переполнился, то ваша база данных откажется работать и будет выдавать ошибку: "журнал транзакций для базы данных заполнен". Почему такое происходит и как этого избежать? Рассмотрим два решения, которые помогут быстро устранить ошибку и продолжить работу с базой.
Увеличиваем размер журнала транзакций.
Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Файлы].
Для типа файла "Журнал" увеличиваем максимальный размера файла для авторасширения.
Сжимаем файл журнала транзакций.
Для сжатия журнала транзакций необходимо модель восстановления базы смнеить на простую, выполнить сжатие журнала, после чего модель восстановления переключить обратно на полную.
Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт [Параметры]. Модель восстановления выбираем "Простая" и нажимаем ОК.
Далее правой клавишей мышки по базе и выбираем из контекстного меню [Задачи] - [Сжать] - [Файлы]
Тип сжатия: Журнал
Операция сжатия: Реорганизовать файлы, перед тем как освободить неиспользуемое место
И указываем размер до которого необходимо сжать, например 0.
Теперь нужно вернуться в свойства базы к пункту [Параметры] и переключить модель восстановления на "Полная".
Операции резервного копирования, усечения и сжатия журнала транзакций SQL Server
В этой статье мы рассмотрим операции резервного копирования журналов транзакций SQL Server, операции усечения и сжатия с обзором и примерами, охватывающими все обсуждаемое.
Если эта статья является вашим первым посещением серии журналов транзакций SQL Server, я рекомендую вам ознакомиться с предыдущими статьями (см. Оглавление ниже), в которых мы описали внутреннюю структуру журнала транзакций SQL Server, важную роль, которую выполняет транзакция. Журнал играет в поддержании базы данных в согласованном состоянии и восстановлении поврежденной базы данных или ошибочно измененной таблицы до определенного момента времени.В этой серии мы также обсудили три модели восстановления: полное, простое и с неполным протоколированием, которые контролируют, как транзакции будут записываться в файл журнала транзакций SQL Server, и, наконец, как управлять ростом журнала транзакций SQL Server и контролировать его.
Собрав всю основную информацию из предыдущих статей, теперь мы готовы подробно обсудить в этой статье разницу между концепциями резервного копирования, усечения и сжатия журнала транзакций SQL Server и способы выполнения этих операций.
Резервное копирование журнала транзакций
При настройке вашей базы данных с использованием простой модели восстановления журнал транзакций SQL Server будет помечен как неактивный и автоматически усечен после фиксации активной транзакции. Это не относится к моделям восстановления базы данных с полным и неполным протоколированием. Когда для базы данных настроена модель полного восстановления, журнал транзакций SQL Server в файле журнала транзакций будет помечен как неактивный после фиксации транзакции без автоматического усечения, так как это будет , ожидающее выполнения резервного копирования журнала транзакций .Напомним, что только резервная копия журнала транзакций, но НЕ полная резервная копия базы данных, будет усекать журналы транзакций из файла журнала транзакций и делать его доступным для повторного использования. Если из базы данных не берется резервная копия журнала транзакций, файл журнала транзакций будет постоянно увеличиваться без усечения, пока в нем не закончится свободное пространство.
Резервная копия журнала транзакций SQL Server может быть взята только из базы данных, если модель восстановления этой базы данных является полной или с неполным протоколированием.Модель восстановления базы данных можно проверить на вкладке Options окна Database Properties, как показано ниже:
Если вы попытаетесь сделать резервную копию журнала транзакций для базы данных, для которой настроена модель простого восстановления, операция резервного копирования завершится сбоем с сообщением об ошибке ниже:
Кроме того, для резервного копирования журнала транзакций требуется, чтобы из этой базы данных была взята хотя бы одна полная резервная копия в качестве начальной точки для новой цепочки резервных копий.Если вы попытаетесь сделать резервную копию журнала транзакций из базы данных, в которой ранее не выполнялось полное резервное копирование, операция резервного копирования завершится сбоем с сообщением об ошибке ниже:
Давайте сделаем полную резервную копию базы данных, чтобы иметь возможность делать резервную копию журнала транзакций для этой базы данных. Мы будем использовать команду T-SQL BACKUP DATABASE для выполнения операции полного резервного копирования базы данных в нашем примере. Дополнительные сведения о различных способах и вариантах выполнения резервного копирования базы данных в SQL Server см. В серии «Резервное копирование и восстановление SQL Server».Полную резервную копию базы данных можно сделать с помощью сценария T-SQL ниже:
| РЕЗЕРВНАЯ БАЗА ДАННЫХ [TSQL] НА ДИСК = N'C: \ Ahmad Yaseen \ TSQL.bak 'WITH NOFORMAT, NOINIT, NAME = N'TSQL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, СТАТИСТИКА = 10 GO |
После того, как будет выполнено полное резервное копирование базы данных, мы начнем резервное копирование журнала транзакций для базы данных.Первая резервная копия журнала транзакций создаст резервную копию всех транзакций, произошедших в базе данных с момента последней полной резервной копии. Резервную копию журнала транзакций можно сделать с помощью команды T-SQL BACKUP LOG ниже:
| РЕЗЕРВНЫЙ ЖУРНАЛ [TSQL] НА ДИСК = N'C: \ Ahmad Yaseen \ TSQL_2.TRN 'WITH NOFORMAT, NOINIT, NAME = N'TSQL-TRN Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, СТАТИСТИКА = 10 GO |
С другой стороны, резервные копии журнала транзакций, которые следует за , первая резервная копия журнала транзакций будет создавать резервные копии для всех транзакций, которые произошли в базе данных с момента остановки последнего резервного копирования журнала транзакций.Полная резервная копия и все последующие резервные копии журнала транзакций до создания новой полной резервной копии называются Backup Chain . Эта цепочка резервного копирования важна для восстановления базы данных до определенного момента времени в случае любого ошибочно выполненного изменения или повреждения базы данных. Частота резервного копирования журнала транзакций зависит от того, насколько важны ваши данные, размер базы данных и какой тип рабочей нагрузки она обслуживает. В базах данных с большим количеством транзакций рекомендуется увеличить частоту резервного копирования журнала транзакций, чтобы минимизировать потерю данных, и усечь журналы транзакций, чтобы сделать их доступными для повторного использования.
Если база данных повреждена, рекомендуется создать резервную копию конечного журнала, чтобы вы могли восстановить базу данных до текущего момента времени. Резервное копирование хвостового журнала используется для сбора всех записей журнала, для которых еще не было выполнено резервное копирование. Это поможет предотвратить потерю данных и сохранить целостность цепочки журналов.
Предположим, что вы по ошибке выполнили приведенную ниже инструкцию DELETE, не указав предложение WHERE. Это означает, что все записи таблицы будут удалены:
Если вы разработали подходящее решение для резервного копирования, данные можно легко восстановить, восстановив базу данных до определенного момента времени перед выполнением оператора DELETE.В окне «Восстановить базу данных» SQL Server вернет полную цепочку резервных копий, взятую из этой базы данных. Если вы знаете точный файл, который берется непосредственно перед удалением данных, вы можете остановиться на этом конкретном файле, как показано ниже:
Но если вам известно точное время выполнения оператора DELETE, вы можете восстановить базу данных обратно в этот конкретный момент времени до выполнения оператора DELETE, без необходимости знать, какой файл журнала транзакций содержит этот момент времени.Этого можно достичь, щелкнув опцию Timeline и указав время, как показано ниже:
Усечение журнала транзакций
Усечение журнала транзакций SQL Server - это процесс, в котором все VLF, помеченные как неактивные, будут удалены из файла журнала транзакций SQL Server и станут доступны для повторного использования. Если в VLF есть одна активная запись журнала , общий VLF будет считаться активным журналом и не может быть усечен.
Журнал транзакций SQL Server для базы данных, настроенной с использованием модели восстановления Simple , может быть автоматически усечен, если:
- Сработал оператор контрольной точки
- Транзакция базы данных зафиксирована
Журнал транзакций SQL Server для базы данных, для которой настроена модель восстановления Full или Bulk-Logged , может быть усечен автоматически:
- После выполнения процесса резервного копирования журнала транзакций, когда журнал транзакций не ожидает активной транзакции или какой-либо функции высокой доступности, такой как зеркалирование, репликация или группа доступности AlwaysOn
-
Измените модель восстановления базы данных на Simple
Например, если мы изменим модель восстановления указанной ниже базы данных на простую и выполним контрольную точку напрямую, журнал транзакций будет автоматически усечен и будет доступен для повторного использования, как показано ниже: -
TRUNCATE_ONLY Параметр резервного копирования журнала транзакций, который разрывает цепочку резервного копирования базы данных и усекает доступные журналы транзакций.(Доступно только до SQL Server 2008.)
Если вы попытаетесь усечь журнал транзакций базы данных с помощью параметра TRUNCATE_ONLY в экземпляре SQL Server версии 2008 и более поздних версий, выполнение инструкции завершится ошибкой с сообщением об ошибке ниже:
Уменьшение журнала транзакций
Когда файл журнала транзакций базы данных усекается, усеченное пространство освобождается и становится доступным для повторного использования.Но размер файла журнала транзакций не будет уменьшен, так как усеченное пространство не будет освобождено. С другой стороны, процесс восстановления пространства журнала транзакций путем освобождения свободных VLF и возврата их обратно в операционную систему называется сжатием журнала транзакций . операция.
Операция сжатия файла журнала транзакций может быть выполнена, только если в файле журнала транзакций есть свободное место, которое может быть доступно большую часть времени после усечения неактивной части журнала транзакций.Операция сжатия будет полезна после выполнения операции, которая создает большое количество журналов транзакций.
Файл журнала транзакций базы данных можно уменьшить, щелкнув правой кнопкой мыши базу данных и выбрав параметр «Сжать» -> «Файлы» в меню «Задачи», как показано ниже:
На странице «Сжать файл» измените Тип файла на Журнал и выберите файл журнала транзакций, который нужно сжать. На этой странице у вас есть три варианта:
Тот же файл журнала транзакций можно сжать с помощью инструкции DBCC SHRINKFILE T-SQL ниже:
| ИСПОЛЬЗОВАТЬ [AdventureWorks2016CTP3] GO DBCC SHRINKFILE (N'AdventureWorks2016CTP3_Log ', 0, TRUNCATEONLY) GO |
Сжать файл журнала транзакций до размера, меньшего, чем размер виртуального файла журнала, невозможно, даже если это пространство не используется.Это связано с тем, что файл журнала транзакций можно сжать только до границы VLF. В этом случае ядро СУБД SQL Server освободит как можно больше места, а затем выдаст информационное сообщение, как показано ниже:
В следующей статье этой серии мы обсудим лучшие практики, которые следует применять к журналу транзакций, чтобы получить от него оптимальную производительность. Следите за обновлениями!
Содержание
Ахмад Ясин (Ahmad Yaseen) - инженер Microsoft по большим данным с глубокими знаниями и опытом в областях SQL BI, администрирования баз данных SQL Server и разработки.Он является сертифицированным специалистом по решениям Microsoft в области управления данными и аналитикой, сертифицированным партнером по решениям Microsoft в области администрирования и разработки баз данных SQL, партнером разработчика Azure и сертифицированным инструктором Microsoft.
Кроме того, он вносит свои советы по SQL во многие блоги.
Посмотреть все сообщения от Ahmad Yaseen
Последние сообщения от Ahmad Yaseen (посмотреть все) .Как обрезать строку в SQL Server до 2017 года?
Переполнение стека- Около
- Товары
- Для команд
- Переполнение стека Общественные вопросы и ответы
- Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
- Вакансии Программирование и связанные с ним технические возможности карьерного роста
- Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
- Реклама Обратитесь к разработчикам и технологам со всего мира
- О компании
MS SQL 2008/2012 HRESULT = 80040E14
MS SQL:
:
Поставщик Microsoft OLE DB для SQL Server: «ReportServer». `` log_reuse_wait_desc
sys. базы данных HRESULT = 80040E14, SQLStvr: состояние ошибки = 2, серьезность = 11, собственный = 9002, строка = 1
:
Поставщик Microsoft OLE для SQL Server: журнал транзакций для базы данных «ReportServer» заполнен. Чтобы узнать, почему пространство в журнале нельзя использовать повторно, см. Столбец log_reuse_wait_desc sys.база данных
HRESULT = 80040E14, SQLSTATE = 4 2000, собственный = 9002
,,. , MS SQL.
(* .ldf), (). ,.
:
1. (простой). - (Свойства) - (Параметры) - 4- (Модель восстановления) - (Простой) - ОК.
2. (). - (Задачи) - (Сжать) - (Файлы) - (Тип файла) - (Журнал) - (Сжатие) -, (Реорганизовать страницы перед освобождением неиспользуемого пространства) - (Сжать файл до) -
.
3. (Полный). - (Свойства) - (Параметры) - 4- (Модель восстановления) - (Полное) - ОК.
П.С.:. MS SQL!
.Журнал транзакций- размер файла журнала SQL Server 2008 большой и быстро растет
Переполнение стека- Около
- Товары
- Для команд
- Переполнение стека Общественные вопросы и ответы
- Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
- Вакансии Программирование и связанные с ним технические возможности карьерного роста
- Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
- Реклама Обратитесь к разработчикам и технологам со всего мира
- О компании
Загрузка…
- Авторизоваться зарегистрироваться
-
текущее сообщество
- Переполнение стека Помогите болтать
sql - Как обрезать начальные и конечные пробелы табуляции в запросе MSSQL
Переполнение стека- Около
- Товары
- Для команд
- Переполнение стека Общественные вопросы и ответы
- Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
- Вакансии Программирование и связанные с ним технические возможности карьерного роста
- Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
sql server 2008 r2 - как выполнить откат SQL из журналов транзакций
Переполнение стека- Около
- Товары
- Для команд
- Переполнение стека Общественные вопросы и ответы
- Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
- Вакансии Программирование и связанные с ним технические возможности карьерного роста
- Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
